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

Answer 1

Super late, but this worked for me. Got a bit from @iAmMiee's answer as well as a lot from @reFractil's answer here.

For the code below, the connection, client and username information are all in the excel sheet, and I am trying to find an open connection by name, then an open session with either SE16 or any session without an open transaction window. As I am only able to open 6 sessions max, if I am unable to find an open SE16 session or a free session, then the code will exit requesting to close or free at least 1 session before running it again from the top.

Code may need cleanup, as I have just started learning VBA.

Sub ConnectToSAPOrOpenSAP()

Dim ConnName As String, ClientNumber As String, UserName As String, Conn As String, ConnN1 As String, ConnN2 As String
ConnName = Sheets("Reports").Cells(2, 3)
ClientNumber = Sheets("Reports").Cells(3, 3)
UserName = Sheets("Reports").Cells(4, 3)
Conn = Mid(ConnName, 1, 3)

On Error GoTo NoSAPOpen:

Dim Session
Dim Connection
Dim C As Integer

If Not IsObject(SapGui) Then
    Set SapGui = GetObject("SAPGUI")
    Set Appl = SapGui.GetScriptingEngine
End If
If Not IsObject(Connection) Then
    Set Connection = Appl.Children
    For Each Connection In Appl.Children
        If Connection.Description = ConnName Then
            'MsgBox Connection.Description
            Exit For
        Else
            'MsgBox Connection.Description
        End If
    Next Connection
End If

C = 1
If Not IsObject(Session) Then
    Set Session = Connection.Children
    For Each Session In Connection.Children
        ConnN1 = Conn & "(" & C & ")/" & ClientNumber & " SAP Easy Access"
        ConnN2 = Conn & "(" & C & ")/" & ClientNumber & " Data Browser: Initial Screen"
        If Session.findbyID("wnd[0]").Text = ConnN1 Or Session.findbyID("wnd[0]").Text = ConnN2 Then
            MsgBox Session.findbyID("wnd[0]").Text
            Exit For
        Else
            MsgBox Session.findbyID("wnd[0]").Text
            If C = 6 Then
                MsgBox "You have already reached the maximum number of sessions, please close at least 1 " & Mid(ConnName, 1, 3) & " session before trying again."
                GoTo Retry:
                Exit For
            Else
            End If
        End If
        C = C + 1
    Next Session
End If
If IsObject(WScript) Then
    WScript.ConnectObject Session, "on"
    WScript.ConnectObject SapApplication, "on"
End If

GoTo ConnectedNow:

NoSAPOpen:
    '-------------------------------------------------------------------------------
    ' Open SAP Logon
    '-------------------------------------------------------------------------------
    Dim WSHShell As Object
    Dim proc As Object
    
    Set WSHShell = CreateObject("WScript.Shell")
    Set proc = WSHShell.Exec("C:\Program Files (x86)\SAP\FrontEnd\SapGui\saplogon.exe")
        
        Do Until WSHShell.AppActivate("SAP Logon ")
            application.Wait (Now + TimeValue("0:00:01"))
        Loop
        
    Set SapGui = GetObject("SAPGUI")
    Set Appl = SapGui.GetScriptingEngine
    
    Set Connection = Appl.Openconnection(ConnName, True)
    Set Session = Connection.Children(0)
    
ConnectedNow:
    
    If Session.findbyID("wnd[0]/sbar/pane[0]").Text = "Enter a valid SAP user or choose one from the list" Then
        Session.findbyID("wnd[0]/usr/txtRSYST-MANDT").Text = ClientNumber
        Session.findbyID("wnd[0]/usr/txtRSYST-BNAME").Text = UserName
        Session.findbyID("wnd[0]").sendVKey 0
    End If
    
    '-------------------------------------------------------------------------------
    ' Code to interact to the connected/opened Session below
    '-------------------------------------------------------------------------------
    
    '-------------------------------------------------------------------------------
    ' Test: Check System > User Profile > Own Data > Defaults > Date Format > 2(MM/DD/YYYY)
    '-------------------------------------------------------------------------------
    Dim CurrentKey
    Session.findbyID("wnd[0]").maximize
    Session.findbyID("wnd[0]/tbar[0]/okcd").Text = "/nsu3"
    Session.findbyID("wnd[0]").sendVKey 0
    Session.findbyID("wnd[0]/usr/tabsTABSTRIP1/tabpDEFA").Select

    CurrentKey = Session.findbyID("wnd[0]/usr/tabsTABSTRIP1/tabpDEFA/ssubMAINAREA:SAPLSUID_MAINTENANCE:1105/cmbSUID_ST_NODE_DEFAULTS-DATFM").Key

    Session.findbyID("wnd[0]/tbar[0]/okcd").Text = "/n"
    Session.findbyID("wnd[0]").sendVKey 0

    MsgBox CurrentKey
    
Retry:
    
End Sub

Answer 2

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.

Answer 3

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

Answer 4

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.

1. In one subroutine in the same module, create the object and login to SAP.
2. In other subs/functions do some actions.
3. 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.