VBA excel (1 Viewer)

[Rania01]

Please help me how to shorten the VBA below, I don't want every column I have to make another VBA, maybe use a loop? but I don't understand how. If you can give me advice

The next problem is how to make the name of the export data look like in the column:

A1 = Export Data Range C.xlsx

A2 = Export Data Range D.xlsx

A3= Export Data Range E.xlsx

 

[MarkK]

When you see a repeating block of code, put it in a subroutine. Then, instead of copying and pasting another repeating block of code, call the subroutine.

For a loop, determine the list of items that will control the loop. In your code it looks like C, D, and E, so you could put those in an array, and iterate over that array, one loop per iteration, like....

Sub TestLoop
    Dim varLoop

    For Each varLoop in Split("C D E")  ' Split() returns an array
        LastRow = sht.Range(varLoop & "2").CurrentRegion.Rows.Count
        sht.Range("E2:" & LastRow).Copy
        Call UpdateBlock ' this is your repeating block, in a sub
    Next
End Sub

hth

 

[arnelgp]

same as the above suggestion:

Sub Export()

    If Not IsObject(Application1) Then
        Set SapGuiAuto = GetObject("SAPGUI")
        Set Application1 = SapGuiAuto.GetScriptingEngine
    End If
    If Not IsObject(Connection) Then
        Set Connection = Application1.Children(0)
    End If
    If Not IsObject(session) Then
        Set session = Connection.Children(0)
    End If '


    Dim lastrow As Long
    Dim sht As Worksheet
    Dim var As Variant
    
    Set sht = ThisWorkbook.Worksheets("sheet1")
    
    For Each var In Split("A B C")
        lastrow = sht.Cells(sht.Rows.Count, var).End(xlUp).Row
        Call ExportRoutine(session, sht, var, lastrow)
    Next
    Set sht = Nothing
    
End Sub

Private Sub ExportRoutine(ByRef session As Object, ByRef sht As Worksheet, Byval column As String, Byval lastrow As Long)

    sht.Range(colum & "2:" & column & lastrow).Copy
    session.findById("wnd[0]/tbar[0]/okcd").Text = "/NFBL1N"
    session.findById("wnd[0]").sendVKey 0
    session.findById("wnd[0]/usr/ctxtKD_BUKRS-LOW").Text = "BK01"
    session.findById("wnd[0]/usr/ctxtKD_BUKRS-LOW").SetFocus
    session.findById("wnd[0]/usr/ctxtKD_BUKRS-LOW").caretPosition = 4
    session.findById("wnd[0]/usr/btn%_KD_LIFNR_%_APP_%-VALU_PUSH").press
    session.findById("wnd[1]/tbar[0]/btn[24]").press
    session.findById("wnd[1]/tbar[0]/btn[8]").press
    session.findById("wnd[0]/tbar[1]/btn[8]").press
    session.findById("wnd[0]/mbar/menu[0]/menu[3]/menu[1]").Select
    session.findById("wnd[1]/usr/cmbG_LISTBOX").SetFocus
    session.findById("wnd[1]/tbar[0]/btn[0]").press
    session.findById("wnd[1]/usr/ctxtDY_PATH").Text = "D:\SAP\Data\"
    session.findById("wnd[1]/usr/ctxtDY_FILENAME").Text = "Export Data Range " & column & ".xlsx"
    session.findById("wnd[1]/usr/ctxtDY_FILENAME").caretPosition = 36
    session.findById("wnd[1]/tbar[0]/btn[0]").press

End Sub