Search code examples
excelvbasap-gui

How to copy value from SAP Tree View to Excel sheet using excel VBA

I want to copy the selected stock value of material in Excel sheet from SAP "MMBE" transaction:

If some one could help with the excel VBA syntax to copy the highlighted value in the attached snap.

Solution

  • It is hard to predict column numbers and names although you can record your script with build in SAP recorder and just click proper cell twice to know what indexes it have. It will get more and more complicated if your tables differ from each other. You will need to first get column names you are interested in, then compare it to keys etc. Try to use SAP gui scripting help file, it is right below "SAP scripting and playback" menu option.

    Public SapGuiAuto
Public SetApp
Public Connection
Public session
Public theCell As String 'may be of anything else you need if casted properly

Sub example()

    Dim nameConstructor As String
    nameConstructor = "          "

    Set SapGuiAuto = GetObject("SAPGUI")
    Set SetApp = SapGuiAuto.GetScriptingEngine
    Set Connection = SetApp.Children(0)
    Set session = Connection.Children(0)

    theCell = session.findById("wnd[0]/usr/cntlCC_CONTAINER/shellcont/shell/shellcont[1]/shell[1]").GetItemText(nameConstructor & "6", "C" & nameConstructor & "1")
    MsgBox theCell
    Cells(1, 1) = theCell

End Sub