Skip to Content

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

 

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 ObjectPublic SAPGui As ObjectPublic SAPCon As ObjectPublic SAPSession As Object
'run saplogon if it is not runningSub 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 IfEnd 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 IfEnd Sub
Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

3 Answers

  • Best Answer
    Apr 08, 2013 at 03:02 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member Stefan Schnell

      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.

  • avatar image
    Former Member
    Oct 07, 2015 at 10:21 PM

    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?

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Oct 20, 2015 at 11:32 AM

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

    Add comment
    10|10000 characters needed characters exceeded