Extract SAP data through Excel

Question

Hello!

I’m trying to integrate Excel with SAP via VBA. I would like to run the transaction "MB51" with some parameters like date, deposit and movement and bring the data obtained to the Excel spreadsheet via VBA.
I have been trying to accomplish this task based on the following code:

Sub SAP()

Dim sap As Object
Dim conn As Object

Set sap = CreateObject("SAP.Functions")
Set conn = sap.Connection
conn.System = "S0P"
conn.Client = ""
conn.User = ""
conn.Password = ""
conn.Language = "PT"

If conn.logon(0, False) <> True Then
    MsgBox "Logon to the SAP system is not possible", vbOKOnly, "Comment"
End If


Set SapGuiAuto = GetObject("SAPGUI")          'Utiliza o objeto da interface gráfica do SAP
Set SAPApp = SapGuiAuto.GetScriptingEngine    'Conecta ao SAP que está rodando no momento
Set SAPCon = SAPApp.Children(0)               'Encontra o primeiro sistema que está conectado
Set session = SAPCon.Children(0)              'Encontra a primeira sessão (janela) dessa conexão

If Not IsObject(Application) Then
   Set SapGuiAuto = GetObject("SAPGUI")
End If
If Not IsObject(Connection) Then
End If
If Not IsObject(session) Then
End If
If IsObject(WScript) Then
   WScript.ConnectObject session, "on"
   WScript.ConnectObject Application, "on"
End If
session.findById("wnd[0]").maximize    

Dim objExcel
Dim objSheet, intRow, aux, Log
Set objExcel = GetObject(, "Excel.Application")
Set objSheet = objExcel.ActiveWorkbook.ActiveSheet

DataInicial = Range("H2") 
DataFinal = Range("H3")

session.findById("wnd[0]/tbar[0]/okcd").Text = "/NMB51"

End Sub

I added the "SAP GUI Scripiting API" reference in Excel but whenever the code arrives on the line:

Set SapGuiAuto = GetObject("SAPGUI")

I get the error message

Someone can help me or show me the way?

Answer 1

Try to delete this line,

Set SapGuiAuto = GetObject("SAPGUI")          'Utiliza o objeto da interface gráfica do SAP

Sub testar()

Dim objExcel
Dim objSheet, intRow, aux, Log
Set objExcel = GetObject(, "Excel.Application")
Set objSheet = objExcel.ActiveWorkbook.ActiveSheet

    If Not IsObject(Applicationa) Then
       Set SapGuiAuto = GetObject("SAPGUI")
       Set Applicationa = SapGuiAuto.GetScriptingEngine
    End If

    If Not IsObject(Connection) Then
       Set Connection = Applicationa.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    



DataInicial = Range("H2") 
DataFinal = Range("H3")

session.findById("wnd[0]/tbar[0]/okcd").Text = "/NMB51"

End Sub

Also necessary to activate the option SAP GUI Configuration

Answer 2

Connect Excel - SAP with the script writer. After recording all the script, just replace the fourteen initial lines with the line

Set Session = Getobject("SAPGUI").GetScriptingEngine.Children(0). Children(0)

This connects Excel to SAP