Search code examples
excelvbasap-gui

SAP GridView Get value

I'm trying to get data from SAP Gridview to the excel file,

It worked as for now, however, I've encounter an issue when if the row count is a lot, it will not get everything from the data, for example, my row count is 240, it will only get 52 like that. is there any limitation loop/range from GridView?

So far below is my code:

session.FindById("wnd[0]/tbar[0]/okcd").Text = "/NZFPAYADVINFO"
session.FindById("wnd[0]").sendVKey 0
session.FindById("wnd[0]/usr/ctxtSP$00001-LOW").Text = "AR02"
session.FindById("wnd[0]/tbar[1]/btn[8]").press
session.FindById("wnd[0]/usr/cntlGRID1/shellcont/shell").setCurrentCell -1, "AVIK-AVTXT"
session.FindById("wnd[0]/usr/cntlGRID1/shellcont/shell").selectColumn "AVIK-AVTXT"
session.FindById("wnd[0]/tbar[1]/btn[29]").press
session.FindById("wnd[1]/usr/ssub%_SUBSCREEN_FREESEL:SAPLSSEL:1105/btn%_%%DYN001_%_APP_%-VALU_PUSH").press
session.FindById("wnd[2]/tbar[0]/btn[24]").press
session.FindById("wnd[2]/tbar[0]/btn[8]").press
session.FindById("wnd[1]/tbar[0]/btn[0]").press
session.FindById("wnd[0]/usr/cntlGRID1/shellcont/shell").currentCellColumn = "AVIK-AVSID"


Dim myGrid2 As Object

Set myGrid2 = session.FindById("wnd[0]/usr/cntlGRID1/shellcont/shell")

allRows = myGrid2.RowCount - 1 ' Number of SAP rows

allCols = myGrid2.ColumnCount - 1 ' Number of SAP Columns

Dim columns2 As Object

Set columns2 = myGrid2.ColumnOrder ' SAP column names in order in SAP window

For i = 0 To allRows

   Cells(i + 4, 1).Value = myGrid2.GetCellValue(i, columns2(1))
   
Next i

MsgBox ("Completed")
Solution

  • The backend doesn't send all the grid rows to the frontend, only the ones currently viewed.

    You must scroll the grid via the property FirstVisibleRow, and use the other properties RowCount and VisibleRowCount to scroll only when required.

    Example:

    For rowindex = 0 to grd.RowCount - 1
  ' Position at top if first time or scroll to display next page
  If rowindex mod grd.VisibleRowCount = 0 Then
    grd.FirstVisibleRow = rowindex
  End If

  ' Process the line
  ...
Next

    More information: SAP Library - GuiGridView

    NB: this answer is the copy of my answer for C# and adapted to VBA.