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.

Is your question "how to pass an argument to a subroutine"? What did you try up to now? — Sandra Rossi, Aug 15 '19 at 15:15

Vasily · Accepted Answer · 2019-08-15T22:51:41.317

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

How to use and loop Excel variables in SAP

1 Answers1