I'm currently creating an automation script where data from Excel will be searched in SAP GUI ALV List. I will be looping to the rows that, if it will match anything in the columns "Assignment", "DocumentNo" and "Quantity" to the "textToFind
" in Excel, then I will be able to edit the text for each item matched:
How will I set the table and loop through the rows of the table until I find the text that I'm looking for?
I believe it will also only allow me to search for the visible rows.
I tried to record the steps in SAP GUI but it only gives me this if I position my cursor somewhere in the column "Assignment":
session.findById("wnd[0]").maximize
session.findById("wnd[0]/usr/lbl[18,15]").setFocus
session.findById("wnd[0]/usr/lbl[18,15]").caretPosition = 10
Which I know that it tells me the current cell address (column 18, row 15).
When I tried to check the table name on "Assignment" field (F1), it gives me the name of "RFPOSXEXT
".
Let's assume you display the data in a ALV Grid and you have the session ready as you write in your post. Then the following code will copy the data from SAP into excel. You have to adjust the code according to your needs
Dim wks As Worksheet
Set wks = " your worksheet here ..."
Dim Table As Object
Dim cols As Long
Dim rows As Long
Dim i As Long, j As Long
Set Table = Session.FindById("wnd[0]/usr/cntlGRID1/shellcont/shell/shellcont[1]/shell")
rows = Table.RowCount - 1
cols = Table.ColumnCount - 1
Dim columns As Object
Set columns = Table.ColumnOrder
Dim arrCol() As Variant
ReDim arrCol(cols)
For j = 0 To cols
arrCol(j) = (CStr(columns(j)))
Next
With wks
.Range(.Cells(1, 1), .Cells(1, cols + 1)).Value = arrCol()
End With
For i = 0 To rows
For j = 0 To cols
arrCol(j) = Table.GetCellValue(i, CStr(columns(j)))
Next
With wks
.Range(.Cells(i + 2, 1), .Cells(i + 2, cols + 1)).Value = arrCol()
End With
If i Mod 10 = 0 Then
Table.SetCurrentCell i, CStr(columns(0))
DoEvents
End If
Next
End Sub
The above code will fail if you don't use griv view control. "Session" must be a valid SAP Guisession pointing to FBL3N with the grid view open. In the link I provided above you will see hot to do that.