Extract data from SAP through Excel

Question

Extract data from SAP through Excel

Hello!

I am 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 obtained data 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 reference "SAP GUI scripting API" in Excel but whenever the code arrives on the line:

Set SapGuiAuto = GetObject("SAPGUI")

I get the message from error

Can anyone help me or point the way?

1

excel vba excel-vba sap

Author: Vinicio Schmidt, 2019-02-11

Source

2 answers

score 3 · Answer 1

Try deleting 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

It is also necessary to enable the option SAP GUI Configuration

score 0 · Answer 2

Make the Excel-SAP connection through the script writer. After writing the entire script, just replace the initial fourteen lines with the line

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

This connects Excel to SAP