How to loop through Excel rows-CodePudding

I'm trying to build an Excel VBA script that runs through an Excel sheet and extracts reports from a SAP system and puts them in a specific folder. The parameters start at A10 and B10 and these are put in the SAP screen fields where the report is created and downloaded in the folder on my desktop. Please see here below an example of the Excel sheet and a screenshot of the SAP screen and the code I'm using.

The script works fine for the first parameters A10 and B10 (it extracts a report from the SAP system and puts it in the desired folder) however I would like to loop the script to extract reports automatically for the other parameters below A10 and B10 and beyond (Loop Until ActiveCell.Value = "")

Can someone help me with this? Where and how do I put the loop?

The code:

'Declaring variables for sub procedures

Option Explicit
Public SapGuiAuto, WScript, msgcol
Public objGui  As GuiApplication
Public objConn As GuiConnection
Public session As GuiSession

'Creating sub procedure

Sub SAPCustomerReport()

'Pointing object variables to SAP session

Set SapGuiAuto = GetObject("SAPGUI")
Set objGui = SapGuiAuto.GetScriptingEngine
Set objConn = objGui.Children(0)
Set session = objConn.Children(0)
Dim Vendor As String
Dim CoCo As String
Dim FolderPath As String
Dim SAPOutputLayout As String

Vendor = ActiveWorkbook.ActiveSheet.Range("A10")
CoCo = ActiveWorkbook.ActiveSheet.Range("B10")
FolderPath = ActiveWorkbook.ActiveSheet.Range("B3")
SAPOutputLayout = ActiveWorkbook.ActiveSheet.Range("B4")

'Recorded SAP Script here

session.FindById("wnd[0]").Maximize
session.FindById("wnd[0]/tbar[0]/okcd").Text = "/nFBL1N"
session.FindById("wnd[0]").SendVKey 0
session.FindById("wnd[0]/usr/chkX_SHBV").Selected = True
session.FindById("wnd[0]/usr/chkX_MERK").Selected = True
session.FindById("wnd[0]/usr/chkX_PARK").Selected = True
session.FindById("wnd[0]/usr/ctxtKD_LIFNR-LOW").Text = Vendor
session.FindById("wnd[0]/usr/ctxtKD_BUKRS-LOW").Text = CoCo
session.FindById("wnd[0]/usr/ctxtPA_VARI").Text = SAPOutputLayout
session.FindById("wnd[0]/usr/ctxtPA_VARI").SetFocus
session.FindById("wnd[0]/usr/ctxtPA_VARI").CaretPosition = 12
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/ctxtDY_PATH").Text = FolderPath
session.FindById("wnd[1]/usr/ctxtDY_FILENAME").Text = Vendor & CoCo & ".XLSX"
session.FindById("wnd[1]/usr/ctxtDY_FILENAME").CaretPosition = 4
session.FindById("wnd[1]/tbar[0]/btn[11]").Press

MsgBox "Script Completed."

End Sub

CodePudding user response：

A solution could look like this:

Sub SAPCustomerReport()
...
Recorded SAP Script here
i = 10
do
 session.FindById("wnd[0]").Maximize
 session.FindById("wnd[0]/tbar[0]/okcd").Text = "/nFBL1N"
 ...
 session.FindById("wnd[1]/tbar[0]/btn[11]").Press
 i = i   1
 Vendor = ActiveWorkbook.ActiveSheet.Range("A" & cstr(i))
 CoCo = ActiveWorkbook.ActiveSheet.Range("B" & cstr(i))
loop until Vendor <> ""

MsgBox "Script Completed."

End Sub

Regards, ScriptMan

CodePudding user response：

You should split the code in a routine that iterates over the configuration and one that takes as input vendor and CoCo and creates the report.

Furthermore I suggest to format your configuration (A9:B12) as a table (Ribbon: Insert > table) then you can use the listobject in VBA - which is much easier to handle regarding ranges etc. I named the table "tblSAPReports".

Option Explicit

Sub createAllReports()
Dim lo As ListObject
Set lo = ThisWorkbook.Worksheets("Table1").ListObjects("tblSAPReports") 'adjust to your needs

Dim lr As ListRow
Dim Vendor As String, CoCo As String
For Each lr In lo.ListRows
    Vendor = lr.Range(1, 1): CoCo = lr.Range(1, 2)
    SAPCustomerReport Vendor, CoCo
Next

End Sub




Public Sub SAPCustomerReport(Vendor As String, CoCo As String)

'your code
'but remove Vendor and CoCo variable and the setting of the variables

End Sub