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.
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!
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.
Thanks to the ColumnOrder property we know the rest of the 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").ColumnCountThen 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)
NextNow 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
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.
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”
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 🙂
could i seek your help in helping me on how to code vba to select a specific line is SAP me01?
Hello Andy
Share with us what have You done already 🙂
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
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 🙂
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
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 🙂
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
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?
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)
————————
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?
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
Happy to help.
If any issue connected to scrolling automation in SAP see my article:
https://simpleexcelvba.com/how-to-automate-scrolling-in-sap-table/