VBA Sap Scripting to open or connect/remain in already open session window

Question

I made it ok to automate a SAP transaction data retrieval by using VBA and SAP record and play .vbs script, now I am facing an issue I did not see in the same program in Visual Basic Studio .NET, if SAP Easy Access for my connection of name: LA-6-Prod is already open then Multilogon window appears and breaks my code thus failing the whole thing.

How can I get my code to use the already existing and open connection or open it if it is not open?

It works perfect if LA-6-Prod is not open, but can not tell the end user to make sure no connection windows are open before executing the macro.

Thank you very much

This is my code:

Sub my_sap
  Dim SapGui, Applic, connection, session, WSHShell

  strconnection = Worksheets("sap_info").Range("A2").Value
  Shell "C:\Program Files (x86)\SAP\FrontEnd\SAPgui\saplogon.exe", vbNormalFocus
  Set WSHShell = CreateObject("WScript.Shell")

  Do Until WSHShell.AppActivate("SAP Logon ")
    application.Wait Now + TimeValue("0:00:01")
  Loop

  Set WSHShell = Nothing
  Set SapGui = GetObject("SAPGUI")
  Set Applic = SapGui.GetScriptingEngine

  Set connection = Applic.OpenConnection('LA-6-Prod', True)
  Set session = connection.Children(0)

'===============SAP SCRIPTING===========

'Execute SAP stuffs, I put in here what I recorded from sap record and it works OK

'=======================================

  Set session = Nothing
  connection.CloseSession ("ses[0]")
  Set connection = Nothing
  Set sap = Nothing

End Sub

There is the following answer to loop through all open sessions: https://stackoverflow.com/a/59841341/9150270 — Sandra Rossi, Jan 21 '20 at 17:38

score 2 · Answer 1 · answered Apr 18 '19 at 21:09

You could use a function to test if the connection is already open, using something like this...

Sub sap()

If SAP_Connection Then
MsgBox ("Sap is Open so just attached to session(0)")
Else
MsgBox ("Sap is NOT open so open Logon Window")
End If

End Sub

The function would look like this...

Function SAP_Connection() As Boolean

On Error GoTo ErrSap

If Not IsObject(SapApplication) Then
   Set SapGuiAuto = GetObject("SAPGUI")
   Set SapApplication = SapGuiAuto.GetScriptingEngine
End If
If Not IsObject(Connection) Then
   Set Connection = SapApplication.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 SapApplication, "on"
End If

SAP_Connection = True
Exit Function

ErrSap:
SAP_Connection = False

End Function

score 1 · Answer 2 · answered Apr 18 '19 at 01:08

If you use the SAP BAPI functions in your local client installation, you can bypass the need to execute shell commands etc.

One method in VBA is to create a global variable of type Object in a module for SAP functions.

In one subroutine in the same module, create the object and login to SAP.
In other subs/functions do some actions.
Use a final subroutine to logoff and dispose of the object.

The global variable holds your session open for the duration of your processing.

For example, in your global variables at the top of your module, you could put:

Dim objR3 As Object  'SAP functions / connection

Say you make an Access form with some text fields for the logon attributes (you could also use Excel for this and just reference cells). Then, in your logon subroutine, use something like:

strUser = frm!txtSAPUser
strPwd = frm!txtPassword
strClient = frm!txtClient
strSystem = frm!txtSystem
strServer = frm!txtServer
Set objR3 = CreateObject("SAP.Functions")
With objR3.Connection
    .System = strSystem
    .client = strClient
    .User = strUser
    .Password = strPwd
    .language = "EN"
    .ApplicationServer = strServer
    .SystemNumber = strSystem
    '--if no logon then exit
If .logon(0, True) <> True Then
    MsgBox "Login failed.", vbExclamation, "Login"
End If
End With

Then after doing some actions in other subroutines, you can use a logoff command in your logoff subroutine:

    objR3.Connection.LOGOFF

This method will hold your connection open while you perform some scripting against the SAP interface.

iAmMiee · Answer 3 · 2022-03-25T23:47:22.293

I know I'm a little bit late to this, but maybe somebody wants to know how to interact with a certain sap session (maybe this way is a bit complecated, but it works). So if SAP is already opened and you want SAP to open another session and interact with the new one, you need to know which session number was created. Here is how I get the number:

Function SessNum()

Dim obj_SapGuiAuto As Object
Dim obj_Application As Object
Dim obj_Connection As Object
Dim obj_Session As Object
Dim a As Integer, b As Integer, c As Integer, d As Integer, e As Integer
Dim f As Integer, g As Integer, h As Integer, i As Integer, j As Integer
Dim k As Integer

Set obj_SapGuiAuto = GetObject("SAPGUI")
Set obj_Application = obj_SapGuiAuto.GetScriptingEngine
Set obj_Connection = obj_Application.Children(0)


    Set obj_Session = obj_Connection.Children
    For Each obj_Session In obj_Connection.Children
        a = InStr(obj_Session.ID, "ses[") + 4
        b = InStr(a, obj_Session.ID, "]") - a
        c = Mid(obj_Session.ID, a, b)
        d = d + c
'        Debug.Print c
    Next obj_Session
'    Debug.Print "..." & d & "..."
    e = obj_Connection.sessions.Count
'    Debug.Print e
    If e < 7 Then
        For f = 0 To 6
            If InStr(1, d, f) > 0 Then
                Set obj_Session = obj_Connection.Children(CInt(f))
                obj_Session.createsession
                Do
                    Application.Wait (Now + TimeValue("0:00:01"))
                    If obj_Connection.sessions.Count > e Then Exit Do
                Loop
                Exit For
            End If
        Next f
    Else
        MsgBox "Too many SAP sessions are open.", vbOKOnly, "Problem"
        Exit Function
    End If
    For Each obj_Session In obj_Connection.Children
        g = InStr(obj_Session.ID, "ses[") + 4
        h = InStr(g, obj_Session.ID, "]") - g
        i = Mid(obj_Session.ID, g, h)
        j = j + i
'        Debug.Print i
    Next obj_Session
'    Debug.Print "..." & j & "..."
'    Debug.Print "opened session: " & j - d
    k = j - d

MsgBox "created session: " & k, vbOKOnly, "SessionNumber"


End Function

I am able to open up to 7 sessions, so that's why the variable f can't be bigger the 6 (7 sessions means 0 to 6).

Feedback would be nice, if it helps.

VBA Sap Scripting to open or connect/remain in already open session window

3 Answers3

Linked