How to loop at lines in worksheet?

Question

I get an Excel file with a list of around 500 documents every day and I need to download attachment from SAP system for each document. I recorded a SAP GUI script but I am unable to loop through all the documents. Need help.

I need to enter document number, company code and fiscal year (all will be dynamic"). How do I create a variable and loop to pick values for these three criteria from my Excel sheet?

Public Sub SAPlogin()
    
    Set WshShell = CreateObject("WScript.Shell")
    Set proc = WshShell.Exec("C:\Program Files (x86)\SAP\FrontEnd\SAPgui\saplogon.exe")
               
    Dim temp
    temp = Timer
    Do While Timer - temp < 5
        Loop
    Set SapGui = GetObject("SAPGUI")
    Set Appl = SapGui.GetScriptingEngine
    
    Set Connection = Appl.Openconnection("ERP", True)
    Set session = Connection.Children(0)
    
    session.findById("wnd[0]/usr/txtRSYST-BNAME").Text = "XXXX"
    session.findById("wnd[0]/usr/pwdRSYST-BCODE").Text = "XXXX"
    session.findById("wnd[0]/usr/txtRSYST-LANGU").Text = "EN"
    session.findById("wnd[0]").sendVKey 0
    
    If Not IsObject(Application) Then
       Set SapGuiAuto = GetObject("SAPGUI")
       Set Appl = SapGuiAuto.GetScriptingEngine
    End If
    If Not IsObject(Connection) Then
       Set Connection = Application.Children(0)
    End If
    If Not IsObject(session) Then
       Set session = Connection.Children(0)
    End If
    If IsObject(WScript) Then
       WScript.ConnectObject session, "on"
       WScript.ConnectObject Application, "on"
    End If
    session.findById("wnd[0]").maximize
    session.findById("wnd[0]/tbar[0]/okcd").Text = "fb03"
    session.findById("wnd[0]").sendVKey 0
    session.findById("wnd[0]/usr/txtRF05L-BELNR").Text = "1"
    session.findById("wnd[0]/usr/ctxtRF05L-BUKRS").Text = "20"
    session.findById("wnd[0]/usr/txtRF05L-GJAHR").Text = "2019"
    session.findById("wnd[0]/usr/txtRF05L-GJAHR").SetFocus
    session.findById("wnd[0]/usr/txtRF05L-GJAHR").caretPosition = 4
    session.findById("wnd[0]").sendVKey 0
    session.findById("wnd[0]/titl/shellcont/shell").pressContextButton "%GOS_TOOLBOX"
    session.findById("wnd[0]/titl/shellcont/shell").selectContextMenuItem "%GOS_VIEW_ATTA"
    session.findById("wnd[1]/usr/cntlCONTAINER_0100/shellcont/shell").currentCellColumn = "BITM_DESCR"
    session.findById("wnd[1]/usr/cntlCONTAINER_0100/shellcont/shell").selectedRows = "0"
    session.findById("wnd[1]/usr/cntlCONTAINER_0100/shellcont/shell").pressToolbarButton "%ATTA_EXPORT"
    session.findById("wnd[1]/tbar[0]/btn[0]").press
    session.findById("wnd[0]").sendVKey 12
    session.findById("wnd[0]/usr/txtRF05L-BELNR").Text = "2"
    session.findById("wnd[0]").sendVKey 0
    session.findById("wnd[0]/titl/shellcont/shell").pressContextButton "%GOS_TOOLBOX"
    session.findById("wnd[0]/titl/shellcont/shell").selectContextMenuItem "%GOS_VIEW_ATTA"
    session.findById("wnd[1]/usr/cntlCONTAINER_0100/shellcont/shell").currentCellColumn = "BITM_DESCR"
    session.findById("wnd[1]/usr/cntlCONTAINER_0100/shellcont/shell").selectedRows = "0"
    session.findById("wnd[1]/usr/cntlCONTAINER_0100/shellcont/shell").pressToolbarButton "%ATTA_EXPORT"
    session.findById("wnd[1]/tbar[0]/btn[0]").press
    session.findById("wnd[0]").sendVKey 12

End Sub

Answer 1

Start by simplifying the script you have, breaking it down into smaller components. For example, create a function for logging in, particularly if you are going to start creating a few different functions.

Public Function StartSAPSession() as Variant
    ' Enter your script here for logging in and starting a new session
    '....

    ' Return the session
    Set StartSAPSession = Connection.Children(0)
End Function

I would declare the session object globally so that every time you run a macro you don't need to login and open a new session. Note that using session as lower case will simplify copying and pasting from the SAP GUI Scripting recorder.

Public session as Variant

Then comes actually "doing" what it is that you are after...

Let's assume you have Table1 with the 3 fields you need to query; Document number, Company code and Fiscal year. We will cycle through each of the rows in this table. We will also assume the number of documents may change for each. We check if a session exists (if not create one), populate the query fields, look at the attachments through the context menu and download them. Note: I don't have access to an instance of SAP at this time so don't have a chance of testing this. It has been adapted from previous tools I had compiled. It will need stepping through and doing some testing. I also typically did a lot of testing to understand the errors likely to be received, then find methods of handling them effectively. Anyway, here you go...

Public Function ExtractDocuments()
    Dim Arr() as Variant
    Dim DocNum as String
    Dim Company as String
    Dim FY as String
    Dim AttCnt as Integer
    Dim i as Long
    Dim j as Long

    ' When session is Nothing then we need to create a new session
    ' else assume we can re-use the session 
    If session Is Nothing Then
        Set session = StartSAPSession
    End If

    ' Load the table as an Array, this will be faster
    Arr = Range("Table1").ListObject.DataBodyRange

    ' Cycle through each row of the table (Arr)
    For i = 1 to Ubound(Arr, 1)
        ' Start by loading the row you will enter
        DocNum = Arr(i, 1)
        Company = Arr(i, 2)
        FY = Arr(i, 3)

        With session
            .findById("wnd[0]").maximize
            .StartTransaction "FB03"   ' Load the transaction you are after
            .findById("wnd[0]/usr/txtRF05L-BELNR").Text = DocNum
            .findById("wnd[0]/usr/ctxtRF05L-BUKRS").Text = Company
            .findById("wnd[0]/usr/txtRF05L-GJAHR").Text = FY
            .findById("wnd[0]").sendVKey 0    ' Execute transaction

            ' The query runs and you select context menu and attachments
            .findById("wnd[0]/titl/shellcont/shell").pressContextButton "%GOS_TOOLBOX"
            .findById("wnd[0]/titl/shellcont/shell").selectContextMenuItem "%GOS_VIEW_ATTA"

            ' How many attachments are there? If 1 or more then save each.
            AttCnt = .findById("wnd[1]/usr/cntlCONTAINER_0100/shellcont/shell").RowCount
            If AttCnt > 0 Then
                For j = 0 to AttCnt -1
                    .findById("wnd[1]/usr/cntlCONTAINER_0100/shellcont/shell").selectedRows = j
                    .findById("wnd[1]/usr/cntlCONTAINER_0100/shellcont/shell").pressToolbarButton "%ATTA_EXPORT"
                Next j
                .findById("wnd[1]/tbar[0]/btn[0]").press   ' Exit the Attachments window
            End If
        End With
    Next i
End Function

There's also more opportunity to break this up further into more Subs and Functions to make it more readable and re-usable.

Good luck!