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.

15 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 🙂

      1. could i seek your help in helping me on how to code vba to select a specific line is SAP me01?

      2. Very Helpfull this post, now I have a doubt regarding to the table that is created from the MD04 transaction, I´m trying to get the data from this table but I do not know how to read it

        I´m getting this code from SAP script

        session.findById(“wnd[0]/usr/subINCLUDE1XX:SAPMM61R:0750/tblSAPMM61RTC_EZ/txtMDEZ-EXTRA[5,7]”).setFocus
        session.findById(“wnd[0]/usr/subINCLUDE1XX:SAPMM61R:0750/tblSAPMM61RTC_EZ/txtMDEZ-EXTRA[5,7]”).caretPosition = 6

        1. Hello
          The code You pasted means only that You set focus on probably input box and put the cursor on 6th position in this input box.
          Try to write something there while recording 🙂

  2. Hi, Tomasz Płociński,
    please find the enclosed below line i have recorded this line and getting same line but i got error . pls suggest
    session.findById(“wnd[0]/usr/subSUBTAB:SAPLATAB:0100/tabsTABSTRIP100/tabpTAB02/ssubSUBSC:SAPLATAB:0200/subAREA3:SAPMF02D:7122/txtKNA1-J_1KFREPRE”).Text = sh.Range(“L” & i).Value

    1. Hello Bharat
      What is the error? What is the transaction?
      I need a bigger picture of the situation, best would be if You send me whole code private 🙂

  3. Hey,
    I am trying to follow this method to extract data from hierarchy view but it is giving me error. Please check this script which I recorded from SAP and guide me.

    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
    session.findById(“wnd[0]/tbar[0]/okcd”).text = “md04”
    session.findById(“wnd[0]”).sendVKey 0
    session.findById(“wnd[0]/usr/tabsTAB300/tabpF01/ssubINCLUDE300:SAPMM61R:0301/ctxtRM61R-MATNR”).text = “11001329”
    session.findById(“wnd[0]/usr/tabsTAB300/tabpF01/ssubINCLUDE300:SAPMM61R:0301/ctxtRM61R-BERID”).text = “pk81”
    session.findById(“wnd[0]/usr/tabsTAB300/tabpF01/ssubINCLUDE300:SAPMM61R:0301/ctxtRM61R-WERKS”).text = “pk81”
    session.findById(“wnd[0]/usr/tabsTAB300/tabpF01/ssubINCLUDE300:SAPMM61R:0301/ctxtRM61R-WERKS”).setFocus
    session.findById(“wnd[0]/usr/tabsTAB300/tabpF01/ssubINCLUDE300:SAPMM61R:0301/ctxtRM61R-WERKS”).caretPosition = 4
    session.findById(“wnd[0]”).sendVKey 0
    session.findById(“wnd[0]/usr/subINCLUDE8XX:SAPMM61R:0800/ctxtRM61R-MATNR”).caretPosition = 8
    session.findById(“wnd[0]”).sendVKey 2
    session.findById(“wnd[0]/mbar/menu[3]/menu[1]”).select
    session.findById(“wnd[0]/usr/cntlCC_CONTAINER/shellcont/shell/shellcont[1]/shell[1]”).selectItem ” 4″,”&Hierarchy”
    session.findById(“wnd[0]/usr/cntlCC_CONTAINER/shellcont/shell/shellcont[1]/shell[1]”).ensureVisibleHorizontalItem ” 4″,”&Hierarchy”
    session.findById(“wnd[0]/usr/cntlCC_CONTAINER/shellcont/shell/shellcont[1]/shell[1]”).itemContextMenu ” 4″,”&Hierarchy”

    Last 3 lines are the reference of my pointer when I was trying to copy it manually.

    Kindly help me on this error.

    Thanks

    1. Hello
      Have You tried to add to Watches this line:
      session.findById(“wnd[0]/usr/cntlCC_CONTAINER/shellcont/shell/shellcont[1]/shell[1]”)
      and check the structure as I showed in the article / video?

  4. Hello,

    I’m having some trouble looping through a table that is not so big, ~150 rows)
    The code returns empty (“”) from row 110 forward and I’m not sure why it is happening

    The list is there, I can see it populated in every row

    Piece of code below:
    ————————
    For j = 0 To ((session.findById(“wnd[0]/usr/cntlGRID1/shellcont/shell”).RowCount) – 1)
    myArr(j, 1) = j
    myArr(j, 2) = session.findById(“wnd[0]/usr/cntlGRID1/shellcont/shell”).GetCellValue(j, “LSTAR”)
    Sheet1.Cells(j + 1, 2).Value = myArr(j, 2)
    ————————

    1. Hello Daniel
      The code looks fine at the first glance.
      Are You aware that what You can’t see on the screen You can’t code, in case of SAP? –> does this table contains more rows that You can actually see?

      1. Thanks Tomasz, that was the issue.

        I wasn’t aware of that.
        I added a line that scroll down the table as it loops through the rows and solved the issue

Leave a Reply

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