Excel VBA: How to bind to an active SAP session object?

[former_member213011]

Hi all,

I'm using VBA in Excel 2010 to open SAPlogon 720 using the codes below.

Let say I've run the script once, I'm connected to the SAP server and have one active SAP session, how do I prevent the script from creating another session when I run it again?

Have been reading the Scripting API help file and searching the internet but no hint yet.

Hope someone can help me with this. Really appreciate it.

Thanks,

MSA

Option Explicit
 
Public SAP As Object
Public SAPGui As Object
Public SAPCon As Object
Public SAPSession As Object
'run saplogon if it is not running
Sub OpenSAP()
    Dim SAPTaskID As Double
    Dim SAPLogonPad As String
    SAPLogonPad = "C:\Program Files\SAP\FrontEnd\SAPgui\saplogon.exe"
    On Error Resume Next
    AppActivate "SAP Logon 720"
    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
End Sub

 Sub SAPLogon()
    Call OpenSAP 
      
    AppActivate (ThisWorkbook.Name)
    Static sapID As String
    Static sapPassword As String
    If sapID = "" Or sapPassword = "" Then
        With loginSAP 'a userform to get the sap id and password
        .Show
        sapID = .SAPuser
        sapPassword = .SAPPass
        End With
    End If
     
    If SAPGui Is Nothing Then
        Set SAP = GetObject("sapgui")
        Set SAPGui = SAP.GetScriptingEngine
    End If
    
    If SAPCon Is Nothing Then
        Set SAPCon = SAPGui.OpenConnectionByConnectionString("xxx.xxx.xxx.xxx", True)
    Else
      '(what should I put here to call the SAPCon object already declared from previous run?) 
    End If
    
    If SAPSession Is Nothing Then
       Set SAPSession = SAPCon.Children(0)
        With SAPSession
            .findById("wnd[0]/usr/txtRSYST-MANDT").Text = "200"
            .findById("wnd[0]/usr/txtRSYST-BNAME").Text = sapID
            .findById("wnd[0]/usr/pwdRSYST-BCODE").Text = sapPassword
            .findById("wnd[0]/usr/txtRSYST-LANGU").Text = "EN"
            .findById("wnd[0]").sendVKey 0
        End With
    Else
      '(how do I call the Session already running from previous call to this script?)
     End If
End Sub

[stefan_schnell · Accepted solution]

Hello Sayuti,

in the case that the object variables are set correct, you haven't to do anything.

Sub SAPLogon()

  Call OpenSAP

  Static sapID As String
  Static sapPassword As String

  sapID = "HUGO"
  sapPassword = "BAMBI"

  If SAPGui Is Nothing Then
    Set SAP = GetObject("sapgui")
    Set SAPGui = SAP.GetScriptingEngine
  End If

  If SAPCon Is Nothing Then
    Set SAPCon = SAPGui.OpenConnectionByConnectionString("/H/10.100.100.100/S/3200", True)
  End If

  If SAPSession Is Nothing Then
    Set SAPSession = SAPCon.Children(0)
    With SAPSession
      .findById("wnd[0]/usr/txtRSYST-MANDT").Text = "099"
      .findById("wnd[0]/usr/txtRSYST-BNAME").Text = sapID
      .findById("wnd[0]/usr/pwdRSYST-BCODE").Text = sapPassword
      .findById("wnd[0]/usr/txtRSYST-LANGU").Text = "EN"
      .findById("wnd[0]").sendVKey 0
    End With
  Else
    SAPSession.StartTransaction "se16"
  End If
    
End Sub

This code works. On the first time all if conditions set the variables, but on the second time no object variable is set and with the last else condition the transaction SE16 starts.

An alternative is to destroy all object variables at the end of your program like this

SAPSession = Nothing

With this way you have a defined end state, and I think that's the best course of action.

If a session already exists you can connect to the session with SAPCon.Children(0) like you do, it is the correct way.

You can check the connections with the object connections from GuiApplication and the properties Id, Name etc. Also you can do the same with the sessions object from GuiConnection.

Cheers

Stefan

[former_member213011 · Accepted solution]

Dear Stefan,

Thanks for the tip. Will try it tomorrow at my office.

As you pointed out, I declare the SAP, SAPGui, SAPCon and SAPSession as public objects instead of private object for the procedure to avoid destroying them when the procedure ends. This way, the procedure will skip the 'if SAPGui is Nothing' and 'if SAPCon is Nothing' conditions the second time I run the procedure.

The downside of that, in case the SAPGUI application or the connection to the SAP server is terminated outside of the procedure e.g. connection timeout or 'accidental' logoff/closing the SAPGUI application etc. the script will definitely throw an error.

Is there any way for me to prevent this error e.g let the script check whether the SAPSession object called earlier is still active (i.e. am still connected to the SAP server?) and if the SAPSession is still active, perform SAP transactions on the active connection. If the connection is dead, destroy the current SAPSession object, set a new one, logon to the server then perform SAP transaction from there.

Thanks,

MSA

p/s: there is also an issue with SAP multiple connections, but I'm reserving that for later

[stefan_schnell · Accepted solution]

Hello Sayuti,

here an VBS example how to check the existence of a connection or a session.

'-Begin--------------------------------------------------------------

  '-Function ConnectionExists----------------------------------------
    Function ConnectionExists(oConnection)

      '-Variables----------------------------------------------------
        Dim SAP, SAPGUI, Connections, cntConnection, i, Connection

      Set SAP = GetObject("SAPGUI")
      If Not IsObject(SAP) Then
        Exit Function
      End If

      Set SAPGUI = SAP.GetScriptingEngine()
      If Not IsObject(SAPGUI) Then
        Exit Function
      End If

      Set Connections = SAPGUI.Connections()
      If Not IsObject(Connections) Then
        Exit Function
      End If

      cntConnection = Connections.Count()

      '-Here a loop on the connections to find the correct-----------

        For i = 0 To cntConnection - 1
          Set Connection = SAPGUI.Connections(CLng(i))
          If IsObject(Connection) Then

            '-Here the check with the ID and the name----------------
              If Connection.Id = oConnection.Id And _
                Connection.Description = oConnection.Description Then
                ConnectionExists = vbTrue
                Exit Function
              End If

          End If
        Next

      ConnectionExists = vbFalse

    End Function

  '-Function SessionExists-------------------------------------------
    Function SessionExists(oSession)

      '-Variables----------------------------------------------------
        Dim SAP, SAPGUI, Connections, cntConnection, i, Connection
        Dim Sessions, cntSession, j, Session

      Set SAP = GetObject("SAPGUI")
      If Not IsObject(SAP) Then
        Exit Function
      End If

      Set SAPGUI = SAP.GetScriptingEngine()
      If Not IsObject(SAPGUI) Then
        Exit Function
      End If

      Set Connections = SAPGUI.Connections()
      If Not IsObject(Connections) Then
        Exit Function
      End If

      cntConnection = Connections.Count()

      '-Here the loop on the connections-----------------------------

        For i = 0 To cntConnection - 1
          Set Connection = SAPGUI.Connections(CLng(i))
          If IsObject(Connection) Then

            Set Sessions = Connection.Sessions()
            If IsObject(Sessions) Then

              cntSession = Sessions.Count()

              '-Here the loop on the sessions------------------------

                For j = 0 To cntSession - 1
                  Set Session = Connection.Sessions(CLng(j))
                  If IsObject(Session) Then

                    '-Here the check with the ID and the name--------

                      If Session.Id = oSession.Id And _
                        Session.Name = oSession.Name Then
                        SessionExists = vbTrue
                        Exit Function
                      End If

                  End If

                Next

              End If

          End If
        Next

      SessionExists = vbFalse

    End Function

  '-Sub TestConnection (only for test, not really important)---------
    Sub TestConnection()

      '-Variables----------------------------------------------------
        Dim SAP, SAPGUI, Connection

      Set SAP = GetObject("SAPGUI")
      If SAP Is Nothing Then
        Exit Sub
      End If

      Set SAPGUI = SAP.GetScriptingEngine()
      If SAPGUI Is Nothing Then
        Exit Sub
      End If

      Set Connection = SAPGUI.Connections(0)

      If ConnectionExists(Connection) Then
        MsgBox "Exists"
      Else
        MsgBox "Don't exists"
      End If

    End Sub

  '-Sub TestSession (only for test, not really important)------------
    Sub TestSession()

      '-Variables----------------------------------------------------
        Dim SAP, SAPGUI, Connection, Session

      Set SAP = GetObject("SAPGUI")
      If SAP Is Nothing Then
        Exit Sub
      End If

      Set SAPGUI = SAP.GetScriptingEngine()
      If SAPGUI Is Nothing Then
        Exit Sub
      End If

      Set Connection = SAPGUI.Connections(0)
      Set Session = Connection.Sessions(1)

      If SessionExists(Session) Then
        MsgBox "Exists"
      Else
        MsgBox "Don't exists"
      End If

    End Sub

  '-Main-------------------------------------------------------------
    TestConnection
    TestSession

'-End----------------------------------------------------------------

Hope it is easy enough to understand.

If you have questions, you are welcome.

Cheers

Stefan

[former_member213011 · Accepted solution]

Dear Stefan,

Thanks for the VBS codes. I tested the SessionExists function and It works after minor adjustment to VBA.

Here's the adjusted function:

Function SessionExists(oSession As Object) As Boolean

    SessionExists = False
    Dim SAP As Object, SAPGUI As Object, SAPConnections As Object
    Dim cntConnection As Long, i As Long, SAPConnection As Object
    Dim Sessions As Object, cntSession As Long, j As Long, Session As Object

    Set SAP = GetObject("SAPGUI")
    If Not IsObject(SAP) Then
        Exit Function
    End If

   Set SAPGUI = SAP.GetScriptingEngine
    If Not IsObject(SAPGUI) Then
        Exit Function
    End If

    Set SAPConnections = SAPGUI.Connections()
    If Not IsObject(SAPConnections) Then
        Exit Function
    End If

    cntConnection = SAPConnections.Count()
   
    For i = 0 To cntConnection - 1
        Set SAPConnection = SAPGUI.Connections(CLng(i))
        If IsObject(SAPConnection) Then
            Set Sessions = SAPConnection.Sessions()
            If IsObject(Sessions) Then
                cntSession = Sessions.Count()
                For j = 0 To cntSession - 1
                    Set Session = SAPConnection.Sessions(CLng(j))
                    If IsObject(Session) Then
                        If Session.ID = oSession.ID And Session.Name = oSession.Name Then
                            SessionExists = True
                            Exit Function
                        End If
                    End If
                Next j
            End If
        End If
    Next i
End Function

However, the function still throws the following error at the above underlined part if I closed the SAP session without destroying its VBA object. Otherwise, it works as intended.

Nevertheless, now I get some idea on how to use the Gui object properties after trying your function.

I think I will try a different coding approach to my VBA project.

Thanks again,

MSA

[stefan_schnell · Accepted solution]

Hello Sayuti,

maybe this could help:

If IsObject(Session) And IsObject(oSession) Then

  If Session.Id = oSession.Id And Session.Name = oSession.Name Then
    SessionExists = vbTrue
    Exit Function
  End If

End If

All the best for your project.

Cheers

Stefan

[former_member213011 · Accepted solution]

Dear Stefan,

Thanks! I haven't tested it yet but I'm sure it will work or require minor modification.

BTW, just discovered your Tracker Light tools last night and it is awesome!

My understanding on the SAP scripting objects are now much better after this couple of days then it was when I started a couple of months ago.

You rock!

Thanks again,

MSA

[gediminas_rapalis · Accepted solution]

Hi, not sure if I will get a reply since it's an old thread, but here it goes:

I have the same problem that I declare public objects to keep session alive to SAP (so users don't have to reconfirm popup or need to turn it off security prompt in settings). Just like Sayuti stated, after time out from server side, scripts crash.

I tried to use the solution, but then establishing "local" sessions to check name/id versus "global" will prompt security warning anyway which beats the initial purpose of setting variables as public.

Hope problem makes sense. Is there a way to check if connection is not timed out (avoiding calling SessionExists function as stated above? As I said, the purpose is to avoid Security warning each time macro is run as users run many small macros in short amount of time.

[Former Member]

Does anyone have any ideas as to what I can research?.. I am at my wits end over here

[Former Member]

I have the following code.. which works. except I cannot control the session that I am bound to

   Dim SapGuiApp As Object
   Dim oConnection As Object
   Dim Session As Object
   Dim SAPCon As Object, SAPSesi As Object
   Dim SAPGUIAuto As Object, SAPApp As Object
   'Dim sapConnection As Object
  Dim NumOfRows As Long
  Dim Counter As Long
   Application.ScreenUpdating = False

    NumOfRows = ActiveSheet.UsedRange.Rows.Count - 3
    SessionExists = False
    Dim SAP As Object, SAPGUI As Object, SAPConnections As Object
    Dim cntConnection As Long, i As Long, SAPConnection As Object
    Dim Sessions As Object, cntSession As Long, j As Long
    On Error Resume Next
    Set SAP = GetObject("SAPGUI")
    If Not IsObject(SAP) Then
        Exit Sub
    End If
   Set SAPGUI = SAP.GetScriptingEngine
    If Not IsObject(SAPGUI) Then
        Exit Sub
    End If


    Set SAPConnections = SAPGUI.Connections()
    If Not IsObject(SAPConnections) Then
        Exit Sub
    End If
    cntConnection = SAPConnections.Count()

    For i = 0 To cntConnection - 1
        Set SAPConnection = SAPGUI.Connections(CLng(i))
        If IsObject(SAPConnection) Then
            Set Sessions = SAPConnection.Sessions()
            If IsObject(Sessions) Then
                cntSession = Sessions.Count()
                For j = 0 To cntSession - 1
                    Set Session = SAPConnection.Sessions(CLng(j))
                    If IsObject(Session) Then
                        SessionExists = True
                    End If
                Next j
            End If
        End If
    Next i






   If SAPConnection Is Nothing Then
        MsgBox "Error.. no SAP session could be found"
        Exit Sub
   End If





    With Session

        .findById("wnd[0]").maximize
' Row 1 contains field names, values start in row 2
' Col 1 is the function location
' Col 2 is the Position field value

I get a error when I try to maximise the window.. Any ideas?

[stefan_schnell]

Hello Johann,

I tried your code and it works excellent in my case. It is necessary that one session exists. Are more than one session open, only the last available session is maximized.

Cheers

Stefan

Option Explicit

Sub test()

  Dim SapGuiApp As Object

  Dim oConnection As Object

  Dim Session As Object

  Dim SAPCon As Object, SAPSesi As Object

  Dim SAPGUIAuto As Object, SAPApp As Object

  Dim NumOfRows As Long

  Dim Counter As Long

  Dim SAP As Object, SAPGUI As Object, SAPConnections As Object

  Dim cntConnection As Long, i As Long, SAPConnection As Object

  Dim Sessions As Object, cntSession As Long, j As Long

  Dim SessionExists As Boolean

  Application.ScreenUpdating = False

  NumOfRows = ActiveSheet.UsedRange.Rows.Count - 3

  SessionExists = False

  On Error Resume Next

  Set SAP = GetObject("SAPGUI")

  If Not IsObject(SAP) Then

    Exit Sub

  End If

  Set SAPGUI = SAP.GetScriptingEngine

  If Not IsObject(SAPGUI) Then

    Exit Sub

  End If

  Set SAPConnections = SAPGUI.Connections()

  If Not IsObject(SAPConnections) Then

    Exit Sub

  End If

  cntConnection = SAPConnections.Count()

  For i = 0 To cntConnection - 1

    Set SAPConnection = SAPGUI.Connections(CLng(i))

    If IsObject(SAPConnection) Then

      Set Sessions = SAPConnection.Sessions()

      If IsObject(Sessions) Then

        cntSession = Sessions.Count()

        For j = 0 To cntSession - 1

          Set Session = SAPConnection.Sessions(CLng(j))

          If IsObject(Session) Then

            SessionExists = True

          End If

        Next j

      End If

    End If

  Next i

  If SAPConnection Is Nothing Then

    MsgBox "Error.. no SAP session could be found"

    Exit Sub

  End If

  With Session

   .findById("wnd[0]").maximize 'Last available sessions is maximized

  End With

End Sub

[Former Member]

When I debug it in vba.. and I add a watch for Session, I get the following in the values for a lot of the items in Session:  <Automation error>

I definitely have a SAP Session open already.. Even in my code, I tell it to check to make sure I have a connection or throw a error.

Its kinda like I am bound to the SAP connection.. but something is blocking me from completely binding to the session

[Former Member]

The message I get is:

In debug I have this: