How to use and loop Excel variables in SAP

Question

I want to make a loop in excel that cycles through cells and sets them as variable and uses that variable in SAP. I have the first variable in cell A46.

Sub CustomList()
    Dim LR As Long, i As Long
    Range("A46").Select

    Do
        j = ActiveCell.Value

        Call SAPExportCustom

        ActiveCell.Offset(1).Activate     'Move one cell down
    Loop Until ActiveCell.Value <> ""  'Check if cell still has number
End Sub

--------------------------

Sub SAPExportCustom()
    Set SapGuiAuto = GetObject("SAPGUI")  'Get the SAP GUI Scripting object
    Set SAPApp = SapGuiAuto.GetScriptingEngine 'Get the currently running SAP GUI
    Set SAPCon = SAPApp.Children(0) 'Get the first system that is currently connected
    Set session = SAPCon.Children(0) 'Get the first session (window) on that connection

    session.findById("wnd[0]").maximize
    session.findById("wnd[0]/usr/txt[35,5]").Text = j.Value 'Work center
...
    session.findById("wnd[1]/usr/ctxtDY_FILENAME").Text = j.Value & ".txt"
End Sub

I expect then for the code co back to CustomList, move one cell down to A47 and copy that and run the SAPExportCustom again with new variable.

Answer 1

you should transfer parameter to SAPExportCustom, something like this:

Sub CustomList()
    Dim i As Long, j As String
    i = 46
    j = Cells(i, "A").Value
    While j <> ""
        SAPExportCustom (j)
        i = i + 1
        j = Cells(i, "A").Value
    Wend
End Sub

Sub SAPExportCustom(j As String)
    '...
    session.findById("wnd[0]/usr/txt[35,5]").Text = j
    '...
    session.findById("wnd[1]/usr/ctxtDY_FILENAME").Text = j & ".txt"
End Sub