Updating cells in a GuiTableControl for SM30

[daniel_mccollum]

Hi all,

I've been tinkering with a script to generically update tables via SM30 from an Excel sheet.

V1 required a new recording to get the table update structure for the scripting, & I would loop over the array with variables for the row & column. This was limiting as a new recording would have to be embedded every time I had a new table structure i.e

session.findById("wnd[0]/usr/tblSAPLZTABNAMETCTRL_ZTABNAME/ctxtZTABNAME-ZFIELD[1," & row & "]").text = Cells(nRow, ZFIELD)

V2 I made a conf sheet, & had the compatible tables set up in a lookup table & dynamically retrieved the table format for updates i.e.

session.findById("wnd[0]/usr/" & tGuiTableControl & "/" & tDataIDs(tDataLoopColumn, 1) & "[" & tDataLoopColumn & "," & tDataLoopRow & "]").text = tData(tDataLoopColumn, tTotalLoops)

V3 I am working on to make utterly generic By generating the ID for lookup, i.e.

tGuiTableControl = "wnd[0]/usr/tblSAPL" & TargetTable & "TCTRL_" & TargetTable

'Not able to find pattern matched Table ID Ending immediately (need a more graceful exit longterm)
If session.findById(tGuiTableControl, False) Is Nothing Then
    End
End If
'Found the pattern matched table, assign to tObj for item maintenance
Set tObj = session.findById(tGuiTableControl)

tData = Range("SM30UpdVals")

' Count the array length (dimension 1)
tDataRows = UBound(tData, 1)
' Count the array width (dimension 2)
'tDataColumns = UBound(tData, 2)
' Count the target table width, since thats what we are populating values across.
tDataColumns = tObj.Columns.Count
visiblerows = tObj.VisibleRowCount

tDataLoopRow = 1
tDataLoopColumn = 1
tTotalLoops = 0
tObjItemNum = 0

Do While tDataColumns >= tDataLoopColumn
    Do While visiblerows >= tDataLoopRow
        
        If tData(tDataLoopRow, tDataLoopColumn) <> "" Then
            If tObj.Children.Item(tObjItemNum + 0).Type = "GuiCTextField" Or tObj.Children.Item(tObjItemNum + 0).Type = "GuiTextField" Then
                tObj.Children.Item(tObjItemNum + 0).text = tData(tDataLoopRow, tDataLoopColumn)
                
            ElseIf tObj.Children.Item(tObjItemNum + 0).Type = "GuiComboBox" Then
                tObj.Children.Item(tObjItemNum + 0).Key = tData(tDataLoopRow, tDataLoopColumn)
            Else:
                'other conditions
            End If
        End If
        'n records, reseting for page down
        If tDataLoopRow Mod visiblerows = 0 And tDataLoopRow <> 0 Then
'                tObjItemNum = tObjItemNum + 1
            tDataLoopRow = 0
            Exit Do
        Else: '1st record
            tObjItemNum = tObjItemNum + 1
            tDataLoopRow = tDataLoopRow + 1
        End If
        tTotalLoops = tTotalLoops = 1

    Loop
   
    'n records, next column
    tDataLoopRow = tDataLoopRow + 1
    tObjItemNum = tObjItemNum + 1
    tDataLoopColumn = tDataLoopColumn + 1
    
Loop

Now, to my question, each of these code structures is doing what I want, however for V3, the performance is quite slow, which will outweigh the benefits of being generic.

Im a good enough VBA developer to get things done, but bad enough that I cant identify any way to improve performance.

One thing I look to for what should be achievable, is when simply copy'n'pasting from the sheet manually into the SM30 table, the paste of visible cells happens essentially instantly, so my goal is to emulate that.

Constraints I have discovered:

• pasting cells to a row defined as a combobox fails
• tObj in V3 has the children.items in a 1d array, so I have to convert the 2d excel array into 1d.

If there are any suggestions for improvements, that would be helpful.

Cheers

Daniel

[daniel_mccollum · Accepted solution]

So, improvements to date.

Declared the objects I am operating on more precisely

    Dim tobj As SAPFEWSELib.GuiTableControl
    Dim tObjChild As SAPFEWSELib.GuiComponentCollection<br>

then traversed the items from the tObjChild object

Set tobj = session.findById(tGuiTableControl)
Set tObjChild = tobj.Children

' Count the array length (dimension 1)
tDataRows = UBound(tData, 1)
' Count the target table width, since thats what we are populating values across.
tDataColumns = tobj.Columns.Count
visiblerows = tobj.VisibleRowCount


tDataLoopRow = 1
tDataLoopColumn = 1
tObjRow = 0
tObjItemNum = 0


Do                                                          'Loop over pages to update in depth
    Do While tObjRow < visiblerows                          'Loop over the rows to update in a page
        Do While tDataColumns >= tDataLoopColumn            'Loop over the columns to update in a row
            If tData(tDataLoopRow, tDataLoopColumn) <> "" Then
                If tObjChild.Item(tObjItemNum + 0).Type = "GuiCTextField" Or tObjChild.Item(tObjItemNum + 0).Type = "GuiTextField" Then
                    tObjChild.Item(tObjItemNum + 0).text = tData(tDataLoopRow, tDataLoopColumn)
                    
                ElseIf tObjChild.Item(tObjItemNum + 0).Type = "GuiComboBox" Then
                    tObjChild.Item(tObjItemNum + 0).Key = tData(tDataLoopRow, tDataLoopColumn)
                Else:
                    'other conditions
                End If
            End If
            
            'Are we exiting the updates?
            If tData(tDataLoopRow, 1) = "" Then
                tDataFinished = "X"
                Exit Do
            End If
                    
            tObjItemNum = tObjItemNum + visiblerows
            tDataLoopColumn = tDataLoopColumn + 1


        Loop
        'Are we exiting the updates?
        If tDataFinished = "X" Then
            Exit Do
        End If
        'check if we should commit current batch
        If CommitAfterX > 0 Then
            If tDataLoopRow Mod CommitAfterX = 0 Then
                Call commitCurrentUpdates(session, TargetTable)
                'Reset the Obj Update index
                tObjRow = -1
                'Reset the pattern matched table, assign to tObj for item maintenance
                Set tobj = session.findById(tGuiTableControl)
                Set tObjChild = tobj.Children
            Else:
                x = y
            End If
        End If
        tDataLoopColumn = 1
        tDataLoopRow = tDataLoopRow + 1
        tObjRow = tObjRow + 1
        tObjItemNum = tObjRow
    Loop
    tObjRow = 1
    tObjItemNum = 1
    
    'set next row in preparation for either commit, or next page of visible rows
    session.findById("wnd[0]/mbar/menu[2]/menu[2]").Select
    'check & manage data entry issues.
    Call PreSaveHandler(session)
   
    'Reset the pattern matched table, assign to tObj for item maintenance
    Set tobj = session.findById(tGuiTableControl)
    Set tObjChild = tobj.Children
    
Loop Until tDataFinished = "X"

The screen update is occurring many factors faster in this iteration.

& is close to optimal while naturally dealing with combobox entries which cant be pasted. So Im accepting my own updated code as an answer.

Though if any real developers have interesting ideas to try, please let me know.

Cheers

Daniel