What are the differences in VBA methods to connect to SAP GUI

[jonathan_simcock2]

Hi,

I am trying to use MS Excel VBA to connect to SAP GUI and run a recorded *.vbs script, but there are a number of ways of doing this and I should like to understand what the differences are, and therefore, hopefully figure out which one is best.

I have seen:

Set SAP = CreateObject("SAP.Functions")

Set SAP = CreateObject("Sapgui.ScriptingCtrl.1")

Set SapGuiAuto = GetObject("sapgui")

Set Application = SapGuiAuto.GetScriptingEngine

For my intended use, i.e. using MS Excel VBA to connect to SAP GUI and run a recorded *.vbs script, is there a best syntax to use.  At the moment my code looks (snippet) as follows:

Dim SAP As Object

Dim SAPGui As Object

Dim SAPCon As Object

Dim SAPSession As Object

Dim SAPTaskID As Double

Dim SAPLogonPad As String

Dim WSHShell

Shell "C:\Program Files (x86)\SAP\FrontEnd\SAPgui\saplogon.exe", vbNormalFocus

On Error Resume Next

Set WSHShell = CreateObject("WScript.Shell")

  Do Until WSHShell.AppActivate("SAP Logon ")

    Application.Wait Now + TimeValue("0:00:01")

  Loop

Set WSHShell = Nothing

If Err <> 0 Then

    Err = 0

    SAPTaskID = Shell(SAPLogonPad, vbMinimizedNoFocus)

    If Err <> 0 Then

        MsgBox "Cannot start SAPLOGON", vbCritical, "SAPLogon Failed"

    Else

        'MsgBox "SAP Logon activated " & SAPTaskID, vbInformation, "SAP Running"

    End If

End If

On Error Resume Next

If SAPGui Is Nothing Then

   Set SAP = GetObject("sapgui")

   Set SAPGui = SAP.GetScriptingEngine

End If

On Error Resume Next

If SAPCon Is Nothing Then

   Set SAPCon = SAPGui.OpenConnection(sysName, True)

Else

  'Ready to call if the SAPCon object already declared from previous run

End If

On Error Resume Next

If SAPSession Is Nothing Then

Set SAPSession = SAPCon.Children(0)

    With SAPSession

        .findById("wnd[0]/usr/txtRSYST-MANDT").Text = client

        .findById("wnd[0]/usr/txtRSYST-BNAME").Text = user

        .findById("wnd[0]/usr/pwdRSYST-BCODE").Text = password

        .findById("wnd[0]/usr/txtRSYST-LANGU").Text = "EN"

        .findById("wnd[0]").sendVKey 0

    End With

Else

  'Ready to call the Session already running from previous call to this script

End If

'

'Execute the script

'

Shell "wscript " & path & "\sap.vbs", vbNormalFocus

It works but not sure how robust/clean this approach is.  Whether there is a more direct way to do this.  The Shell to execute the *.vbs is important as I do not want to code the *.vbs into the VBA.  My users need to be able to create/edit any *.vbs and have this VBA run it.

Regards,

Jonathan

[stefan_schnell · Accepted solution]

Hello Jonathan,

welcome in the Scripting Languages forum.

1. Set SAP = CreateObject("SAP.Functions")
   On this way you connect the SAP via the classic RFC library (Remote Function Call).  You can use this connect without an UI and you can use ABAP functionality.
2. Set SAP = CreateObject("Sapgui.ScriptingCtrl.1")
   On this way you open a new SAP Logon instance.
3. Set SapGuiAuto = GetObject("sapgui")
   On this way you use an existing SAP Logon instance.
4. Set Application = SapGuiAuto.GetScriptingEngine
   This returns the application COM interface for the currently running SAP GUI process.
   

Look also here.

Your code snippet looks very good, near by this, Your extensions are very good, e.g. the check if the SAP Logon was started. The only improvement proposal is that one On Error Resume Next is sufficient.

Cheers

Stefan

[jonathan_simcock2 · Accepted solution]

Many thanks Stefan, for your comments and clarification.

I am sure you recognised the re-use of code already provided by forum members, including your own, in my snippet.

I am happy that I understand better what to use, and when.  Thank you also for your On Error comments I shall clean that up.

Regards,

Jonathan