Getting specific cell data from SAP to excel

1
votes

I am trying to get data from specific cells in a row in SAP, into an excel table using VBA. When I record a macro of me clicking the 5-6 items I need, it only gives me that I clicked one. Further more I cannot see any way to get the data from the cells, into SAP. The SAP code is here:

Dim W_Ret As Boolean

W_Ret = Attach_Session
If Not W_Ret Then
    Exit Sub
End If

startrow = 10

On Error GoTo myerr

objSess.findById("wnd[0]").maximize
objSess.findById("wnd[0]/tbar[0]/okcd").Text = "/nymm_pricelist"
objSess.findById("wnd[0]").sendVKey 0
objSess.findById("wnd[0]/usr/ctxtS_MATNR-LOW").Text = Cells(currentline, 1).Value
objSess.findById("wnd[0]/usr/ctxtS_VKORG-LOW").Text = Cells(currentline, 4).Value
objSess.findById("wnd[0]/usr/ctxtS_VKORG-LOW").SetFocus
objSess.findById("wnd[0]/usr/ctxtS_VKORG-LOW").caretPosition = 4
objSess.findById("wnd[0]").sendVKey 8
Set myGrid = session.findById("wnd[0]/usr/cntlGRID1/shellcont/shell")
Cells(currentline, 5).Value = myGrid.GetCellValue(0, "MAKTX")
Cells(currentline, 6).Value = myGrid.GetCellValue(0, "GLOBALSALES_KBETR")
Cells(currentline, 7).Value = myGrid.GetCellValue(0, "GLOBALSALES_KONWA")
Cells(currentline, 8).Value = myGrid.GetCellValue(0, "GLOBALTRANSFER_KBETR")
Cells(currentline, 9).Value = myGrid.GetCellValue(0, "GLOBALTRANSFER_KONWA")


' Setting the line status to completed
Cells(currentline, 2).Value = 1
Exit Sub

myerr:
' Some error occured
' Setting the line status to Failed
Cells(currentline, 2).Value = 2

Any help is appreciated!

2
excelvbasap

2 Answers

4
votes

I solve such issues as follows.

  1. First, I record a help script. In doing so, the relevant columns of the grid are marked one after the other, and e.g. sorted in ascending order. After treating all interested columns in this way, you can discover all column names in the recorded script.

  2. The help script should look something like this:

    . . . session.findById("wnd[0]/usr/cntlGRID1/shellcont/shell").currentCellColumn = "GLOBALTRANSFER_KONWA" session.findById("wnd[0]/usr/cntlGRID1/shellcont/shell").selectColumn "GLOBALTRANSFER_KONWA" session.findById("wnd[0]/usr/cntlGRID1/shellcont/shell").pressToolbarButton "&SORT_ASC"

  3. In the correct script is then e.g. following:

    . . . session.findById("wnd[0]").maximize session.findById("wnd[0]/tbar[0]/okcd").text = "/nymm_pricelist" session.findById("wnd[0]").sendVKey 0 session.findById("wnd[0]/usr/ctxtS_MATNR-LOW").text = Cells(currentline, 1).Value session.findById("wnd[0]/usr/ctxtS_VKORG-LOW").text = Cells(currentline, 4).Value session.findById("wnd[0]/usr/ctxtS_VKORG-LOW").setFocus session.findById("wnd[0]/usr/ctxtS_VKORG-LOW").caretPosition = 4 session.findById("wnd[0]").sendVKey 8 set myGrid = session.findById("wnd[0]/usr/cntlGRID1/shellcont/shell") 'myPar_1 = myGrid.getcellvalue (0 , "X") 'myPar_2 = myGrid.getcellvalue (0 , "Y") 'myPar_3 = myGrid.getcellvalue (0 , "Z") 'myPar_4 = myGrid.getcellvalue (0 , "U") 'myPar_5 = myGrid.getcellvalue (0 , "V") myPar_6 = myGrid.getcellvalue (0 , "GLOBALTRANSFER_KONWA")

For character strings X - V, valid column names are to be used.

Regards, ScriptMan

1
votes

Script man answer works great. For the beginners like me who didn't grasp it at first:

You can create a variable that stores the text by specifying the text location in SAP. Example:

value = session.FindById("wnd[0]/usr/tblSAPL/ctxtAFVGD[4,0]").Text

This saves SAP text to the value variable.

In plain English: 

value =  session.FindById("wnd[0]/usr/TableElement/CellElement[x,y]").Text

' where 
' value stores the text 
' x is column number
' y is row number
' TableElement and CellElement are found by recording
' a SAP script and clicking around on the table to figure out
' how the elements are named