Selecting SAP Fields using a Do Loop VBA

Question

I am working on a macro to go into SAP and select cells in a column to extract to excel. Now if this was excel, this would be no problem as I would simply use a do loop to travel down the column copying along the way. I am in SAP GUI however which is compatible with VBA and it is a little different. I recorded a script of me clicking down a column to see how the code changes. This is what I got.

session.findById("wnd[0]").maximize
session.findById("wnd[0]/usr/lbl[12,13]").setFocus
session.findById("wnd[0]/usr/lbl[12,13]").caretPosition = 6
session.findById("wnd[0]").sendVKey 0
session.findById("wnd[0]/usr/lbl[12,14]").setFocus
session.findById("wnd[0]/usr/lbl[12,14]").caretPosition = 4
session.findById("wnd[0]").sendVKey 0
session.findById("wnd[0]/usr/lbl[12,15]").setFocus
session.findById("wnd[0]/usr/lbl[12,15]").caretPosition = 5
session.findById("wnd[0]").sendVKey 0
session.findById("wnd[0]/usr/lbl[12,16]").setFocus
session.findById("wnd[0]/usr/lbl[12,16]").caretPosition = 5
session.findById("wnd[0]").sendVKey 0
session.findById("wnd[0]/usr/lbl[12,17]").setFocus
session.findById("wnd[0]/usr/lbl[12,17]").caretPosition = 5
session.findById("wnd[0]").sendVKey 0

You can see that a particular value increases from 13 up to 17 as I moved down the column. I figured I could incorporate a do loop to then travel down the column and copy the values. Here is my relevant code

i = 13

Do

session.findById("wnd[0]").maximize
Current_Batch = session.findById("wnd[0]/usr/lbl[12,i]").Text
session.findById("wnd[0]/usr/lbl[12,i]").caretPosition = 6
session.findById("wnd[0]").sendVKey 0

If Current_Batch = "" Then
Exit Do
End If

Cells(i - 11, 4) = Current_Batch


i = i + 1

Loop

This however does not work as it does not recognize the i as a variable. It gives me error 619 (could not find ID)

Does anyone know of a way to make this work?

Answer 1

Yes, I implemented the same kind of script. You need to construct your string, converting i to a string at the same time:

session.findById("wnd[0]/usr/lbl[12," & CStr(i) & "]").caretPosition = 6

CStr(i) converts to string, while & concatenates strings together.

I only changed a single line, but you should be able to do it correctly, since you've identified your problem, so you have a grasp on how things work. Good luck,

Answer 2

Have you tried referring to the columns by name rather than by number?

Here is my loop that captures each column in a row and moves to the next

hRows = session.findById("wnd[0]/usr/cntlGRID1/shellcont/shell").RowCount()
For h = 0 To hRows - 1 'Fill Dispatch Sheet at "A12"
    Cells(h + 12, 4).Value = session.findById("wnd[0]/usr/cntlGRID1/shellcont/shell").getcellValue(h, "ERDAT")
    Cells(h + 12, 5).Value = session.findById("wnd[0]/usr/cntlGRID1/shellcont/shell").getcellValue(h, "KTEXT")
    Cells(h + 12, 6).Value = session.findById("wnd[0]/usr/cntlGRID1/shellcont/shell").getcellValue(h, "ERNAM")
    Cells(h + 12, 1).Value = session.findById("wnd[0]/usr/cntlGRID1/shellcont/shell").getcellValue(h, "GEWRK")
    Cells(h + 12, 2).Value = session.findById("wnd[0]/usr/cntlGRID1/shellcont/shell").getcellValue(h, "AUFNR")
    Cells(h + 12, 3).Value = session.findById("wnd[0]/usr/cntlGRID1/shellcont/shell").getcellValue(h, "USTXT")
Next