Search code examples
excelvbasap-gui

Getting specific cell data from SAP GUI to Excel

I am trying to get data from specific cells in a row in SAP GUI, 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 GUI. The SAP GUI 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!

Solution

  • 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