How to deal with SAP tables – part 1

Recording a value selection from the table in SAP is easy. You just need to record specific cell coordinates. It is different, when the data items in the table change frequently. In today’s article I’m going to show You how to deal with SAP tables.

How to deal with SAP tables - part 1

This is the first part of this article, where I’m going to show You how to deal with tables in SAP on examples.

I will not present here how to log into the SAP.
If You want to know how to do this go here.

Table I

In this example I selected the last row, in the last column of the table. The recorded code looks like this.

session.findById("wnd[0]/usr/cntlGRID_CONT0050/shellcont/shell").setCurrentCell 3, "SGTITLE"

As You can see, there are 2 red brackets in 4th row and 6th column, behind blue rectangle (safety reasons). The last cell is selected by setCurrentCell. You can see in the code that the column name is SGTITLE and row number is 3.
Remember, that enumeration in SAP tables always starts with 0!

How to deal with SAP tables - part 1 table

Just by looking at the table we know, that it has 4 rows and 6 columns. The easiest way to see all the table properties is to go to the Watches.

How to deal with SAP tables - part 1 watches

Thanks to the ColumnOrder property we know the rest of the column names.

How to deal with SAP tables - part 1 table column names

In case of table objects, having as the last in the name /shell, You can always use ColumnCount to count the amount of the columns and RowCount to count the rows.

lastRow = session.findById("wnd[0]/usr/cntlGRID_CONT0050/shellcont/shell").RowCount
col_ctn = session.findById("wnd[0]/usr/cntlGRID_CONT0050/shellcont/shell").ColumnCount

Then gather all the column names in array. To do that use simple loop and get all the items of ColumnOrder property (just like You saw them in the Watches screenshot).

For j = 0 To colOrd_ctn - 1
    colOrd_arr(j) = session.findById("wnd[0]/usr/cntlGRID_CONT0050/shellcont/shell").ColumnOrder.Item(j)
Next

Now You have all the information to easily loop through all cells of that SAP table. In this example we are going to create array table.

To get the cells value You can use .getCellValue, where You need to pass number of row and column name.

ReDim SAPTable_arr(0 To lastRow - 1, 0 To col_ctn - 1)

For j = 0 To col_ctn - 1
    For i = 0 To lastRow - 1
        SAPTable_cell = session.findById("wnd[0]/usr/cntlGRID_CONT0050/shellcont/shell").getCellValue(i, colOrd_arr(j))
        SAPTable_arr(i, j) = SAPTable_cell
    Next
Next
How to deal with SAP tables - part 1 watches array

Of course You don’t have to duplicate the table with array, looping through You can check columns or rows to find certain data.

Code

After all description here comes the whole code together.

'Connect to SAP
'SAP transaction recording
'...

session.findById("wnd[0]/usr/cntlGRID_CONT0050/shellcont/shell").setCurrentCell 3, "SGTITLE"

Dim i As Long, j As Long, col_ctn As Long, lastRow As Long
Dim colOrd_arr As Variant, SAPTable_arr As Variant
Dim SAPTable_cell As String

lastRow = session.findById("wnd[0]/usr/cntlGRID_CONT0050/shellcont/shell").RowCount
col_ctn = session.findById("wnd[0]/usr/cntlGRID_CONT0050/shellcont/shell").ColumnCount
ReDim colOrd_arr(0 To col_ctn - 1)

For j = 0 To col_ctn - 1
    colOrd_arr(j) = session.findById("wnd[0]/usr/cntlGRID_CONT0050/shellcont/shell").ColumnOrder.Item(j)
Next

ReDim SAPTable_arr(0 To lastRow - 1, 0 To col_ctn - 1)

For j = 0 To col_ctn - 1
    For i = 0 To lastRow - 1
        SAPTable_cell = session.findById("wnd[0]/usr/cntlGRID_CONT0050/shellcont/shell").getCellValue(i, colOrd_arr(j))
        SAPTable_arr(i, j) = SAPTable_cell
    Next
Next

Table I – summary

It was not so hard as it seemed at the beginning. In the end I was surprised that in the past I just didn’t know what to do at all.

That was the first example, I got for You one more case of table with check boxes. So stay tuned for the second part of how to deal with SAP tables.

Author: Tomasz Płociński

I'm very advanced in VBA, Excel, also easily linking VBA with other Office applications (e.g. PowerPoint) and external applications (e.g. SAP). I take part also in RPA processes (WebQuery, DataCache, IBM Access Client Solutions) where I can also use my SQL basic skillset. I'm trying now to widen my knowledge into TypeScript/JavaScript direction.

3 thoughts on “How to deal with SAP tables – part 1”

  1. Hi thanks a lot for this beautiful guide! I dont know is it write place to ask a problem regarding my problem. Here what I am facing in SAP;
    I would like to change the workcenters in the operations list of tasklists if the workcenter is not correct. Like mechanical to electrical. However, I don’t know how to make loop through the list (every row’s workcenter needs to be checked if it is not true then it will be written with correct value) I would like to share work the uncomplete code with you. Any help will be appreciated. Thanks for your help and concern. (By the way, I run this code using SAP’s playback function)

    If Not IsObject(application) Then
    Set SapGuiAuto = GetObject(“SAPGUI”)
    Set application = SapGuiAuto.GetScriptingEngine
    End If
    If Not IsObject(connection) Then
    Set connection = application.Children(0)
    End If
    If Not IsObject(session) Then
    Set session = connection.Children(0)
    End If
    If IsObject(WScript) Then WScript.ConnectObject session, “on”
    WScript.ConnectObject application, “on”
    End If

    session.findById(“wnd[0]”).maximize

    Rem ADDED BY EXCEL ***************************************

    Dim objExcel

    Dim objSheet, i

    Set objExcel = GetObject(, “Excel.Application”)

    Set objSheet = objExcel.ActiveWorkbook.ActiveSheet

    For i = 2 To objSheet.UsedRange.Rows.Count

    COL1 = Trim(CStr(objSheet.Cells(i, 1).Value)) ‘Group

    COL2 = Trim(CStr(objSheet.Cells(i, 2).Value)) ‘Counter

    COL3= Trim(CStr(objSheet.Cells(i, 3).Value)) ‘WorkCenter

    Rem ADDED BY EXCEL ****************************************

    session.findById(“wnd[0]/tbar[0]/okcd”).text = “/NIA06”

    session.findById(“wnd[0]”).sendVKey 0 session.findById(“wnd[0]/usr/ctxtRC271-PLNNR”).text = COL1 ‘Group session.findById(“wnd[0]/usr/txtRC271-PLNAL”).text = COL2 ‘Counter

    session.findById(“wnd[0]/usr/txtRC271-PLNAL”).setFocus

    session.findById(“wnd[0]/usr/txtRC271-PLNAL”).caretPosition = 1

    session.findById(“wnd[0]”).sendVKey 0

    session.findById(“wnd[0]/usr/tblSAPLCPDITCTRL_3400/ctxtPLPOD-ARBPL[2,1]”).text = “DEPT-MEC” ‘WorkCenter will be replaced with this always if the workcenter is not correct session.findById(“wnd[0]/usr/tblSAPLCPDITCTRL_3400/ctxtPLPOD-ARBPL[2,1]”).setFocus session.findById(“wnd[0]/usr/tblSAPLCPDITCTRL_3400/ctxtPLPOD-ARBPL[2,1]”).caretPosition = 8 session.findById(“wnd[0]”).sendVKey 0 session.findById(“wnd[0]”).sendVKey 0

    session.findById(“wnd[0]”).sendVKey 0 session.findById(“wnd[0]/tbar[0]/btn[0]”).press session.findById(“wnd[0]/tbar[0]/btn[11]”).press

    objExcel.Cells(i, 4).Value = session.findById(“wnd[0]/sbar”).Text ‘SystStat

    Rem FINALIZATION CONTROL CHECK ***************************** aux=COL1 & ” ” & COL2 CreateObject(“WScript.Shell”).run(“cmd /c @echo %date% %time% ” & aux & ” >> C:\Local\Script\CreationLog.txt”) Next msgbox “Process Completed”

    1. Hello!
      You can always contact me via email 🙂
      If You want to loop use For Loop to go line by line, if You want to check something until its done – use Do While / Do Until
      Same as You do in Excel, but using SAP objects 🙂

Leave a Reply

Your email address will not be published. Required fields are marked *