Skip to Content

Updating cells in a GuiTableControl for SM30

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Best Answer
    Oct 19, 2017 at 04:59 AM

    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

    Add comment
    10|10000 characters needed characters exceeded