ALV grid only loads first 64 rows, how to change default load

Question

Situation

I have created a lookup functionality for SAP GUI scripting.
If a grid row has specific values in specific columns, then it is double clicked (this triggers loading specific dependent data).
My grid has less than 300 rows, so loading so much data shouldn't strain a modern computer.

Issue

The issue I have is that from SAPGrid Row 64 it returns "" for each cell. If I enter debugging and scroll down in the ALV grid then the grid row loads and the results are found.

Possible solutions

Can I change how many rows are loaded on default?
Is there a method for pulling the full recordset?
The alternative options include scrolling up and down using scripting or setting up filters.

Code

Sub FindGridLine(SAPGrid As Object, criteria() As String)
SAPGrid.ClearSelection 'first it deselects what has been selected

For k = 0 To (SAPGrid.RowCount - 1) 'for each grid row
    For i = 1 To UBound(criteria, 1) ' for each criteria row except for the first (should be only 1)
        For j = LBound(criteria, 2) To UBound(criteria, 2) 'and for each column
            tempstr = SAPGrid.GetCellValue(k, criteria(0, j))
            If tempstr <> criteria(i, j) Then 'if the criterion doesn't match
                GoTo nextrow 'then go to the next row
            End If
        Next j
    Next i
    'if it passed the criteria then doubleclick it
    SAPGrid.DoubleClick k, criteria(0, 0)
    Exit Sub
nextrow:
Next k
'in case no results were found
MsgBox "No line was found in grid!"
End Sub

Updated code

Code updated based on correct answer from @Asger.
Since lookups mostly work with primary keys, I went for the safe solution of SAPGrid.GetCellValue(k, criteria(0, j)) = "" but the solution is in fact SAPGrid.SetCurrentCell k, criteria(0, j).

Sub FindGridLine(SAPGrid As Object, criteria() As String)
'    SAPGrid.SelectAll 'first it selects everything as to load the full grid
    SAPGrid.ClearSelection 'first it deselects what has been selected
    
    For k = 0 To (SAPGrid.RowCount - 1) 'for each grid row
        For i = 1 To UBound(criteria, 1) ' for each criteria row except for the first (should be only 1)
            For j = LBound(criteria, 2) To UBound(criteria, 2) 'and for each column
                tempstr = SAPGrid.GetCellValue(k, criteria(0, j))
                If tempstr = "" Then SAPGrid.SetCurrentCell k, criteria(0, j) 'this solution only works if the search is done in a non-empty field
                tempstr = SAPGrid.GetCellValue(k, criteria(0, j))
                If tempstr <> criteria(i, j) Then 'if the criterion doesn't match
                    GoTo nextrow 'then go to the next row
                End If
            Next j
        Next i
        'if it passed the criteria then doubleclick it
        SAPGrid.DoubleClick k, criteria(0, 0)
        Exit Sub
nextrow:
    Next k
'in case no results were found
For i = 0 To UBound(criteria, 1) ' for each criteria row except for the first (should be only 1)
    For j = LBound(criteria, 2) To UBound(criteria, 2) 'and for each column
        tempstr = tempstr & "|" & criteria(i, j)
    Next j
    If i <> UBound(criteria, 1) Then
        tempstr = tempstr & vbNewLine
    End If
Next i
MsgBox "No line was found in grid!" & vbNewLine & "Please select line" & tempstr & vbNewLine & "manually and press 'OK'" & vbNewLine & "or enter debug mode."
End Sub

Since canonical answers are not available (at least my Google search was not successful) I offer a reasonable bounty. Note that the code is from Excel VBA but as far as I know it fits all VBA environments, possibly VBScript as well. — user3819867, Feb 18 '19 at 08:11
As your "Updated code" block seems to be a valid solution, please move it into a distinct answer so that to better organize the post, and make it more visible to the visitors. Thank you! — Sandra Rossi, Feb 02 '23 at 18:19
Note that there's this [other question](https://stackoverflow.com/questions/65576298/cant-read-all-rows-from-guigridview-getting-blank-after-34-rows) posted later on the same topic, and answered. — Sandra Rossi, Feb 02 '23 at 18:21

Asger · Accepted Answer · 2019-02-18T13:17:03.427

3

GuiGridView / ALV Grid Control: For large amounts of data, reloading of the content takes place only after scrolling, otherwise it is likely that only one empty string will be returned as the result - even without causing an exception.

Therefore SetCurrentCell should always be used to focus and load the dataset to be read.

Please test e. g. SAPGrid.SetCurrentCell(k, 1)

Maybe it's sufficient to load every new 64 rows (I can't test it):

If k Mod 64 = 63 Then ' at least if 1 row before each 64 rows
    SAPGrid.SetCurrentCell (k, criteria(0, LBound(criteria, 2)))
End If

edited Feb 18 '19 at 13:17

answered Feb 18 '19 at 13:01

Asger

3,822
3
12
37

I will test if it works, if it does then I will mark your answer correct and update my post with the modified script. – user3819867 Feb 18 '19 at 13:20
I cannot yet give away the bounty, please prompt me in 16 hours. The GUI looks awkward, but the SetCurrentCell method surprisingly doesn't trigger much of the built-in checks so for 244 rows it ran in just 15 seconds including initialization (for GUI scripting it is OK). – user3819867 Feb 18 '19 at 15:55
In your updated code, there's no `SetCurrentCell` at all?! – Asger Feb 18 '19 at 15:58
`If tempstr = "" Then SAPGrid.SetCurrentCell k, criteria(0, j)`, I am looking at primary keys, so in this case the `tempstr` is never a zero length string. – user3819867 Feb 18 '19 at 15:59
1

Sorry ./ Did you try the `If k Mod 64 = 63` directly after your code row `For k = 0 ...`? Maybe it's enough to `SetCurrentCell` every 64 rows. – Asger Feb 18 '19 at 16:03
In case I do that the OS notes rolled out may increase this limit to e.g. 128 and for older systems it may still be e.g. 32. There's no one-size-fits-all, as long as we're looking at primary keys the one I have in the question should work. – user3819867 Feb 18 '19 at 16:08
@user3819867 Thank you for assigning that bounty to my answer! I feel a little ashamed to earn such a large amount of reputation points from you, as you earned them by 160 answers some years ago! So your badge "unsung hero" is still applicable in my opinion :) – Asger Feb 19 '19 at 15:22
I answered questions mostly with well documented resolutions, you answered something that I couldn't find on SAP forums. It may seem trivial to you but within 60 hours of posting the questions I didn't even get clarifying questions. This is the answer I was saving up reputation for. – user3819867 Feb 19 '19 at 16:05
Also note that it is 64 rows with your equipment, but may vary if you use a different laptop or different monitor with different size and resolution (random I know). We often used to scroll every 20-30 rows, we found it avoided problems across the whole range of standard equipment issued within company I worked for. – Josh Feb 24 '19 at 14:05
This is awesome, I spent the better part of a day trying to figure this out. Our grid was 360 lines long, we got cell data on the first 100 and the bottom 50 which had us chasing our tails. This worked great. Thanks! – Kassabba Nov 03 '20 at 20:33