VBA scripting - is it possible to log on by SID rather than by name

[markhamlyn]

I have a VBA script that logs into SAP, inputs some data from an Excel sheet, pulls some results and copies them back to Excel. The script works fine on my machine, but not on some others, and I have discovered that this is because the "Description" specified in the System Connection Parameters is not the same on every machine (no idea why). To get around this, I'm wondering if it is possible to use the System ID field instead, as that is the same on all machines here:

The relevant portion of the code is here:

If Not IsObject(SAPguiApp) Then
On Error Resume Next
Set SAPguiAuto = GetObject("SAPGUI")
If Err.Number <> 0 Then
On Error GoTo 0
Set WshShell = CreateObject("WScript.Shell")
Set oExec = WshShell.Exec("C:\Program Files (x86)\SAP\FrontEnd\SAPgui\saplogon.exe")

Do While Not WshShell.AppActivate("SAP Logon 750")
sleep 1
Loop
Set SAPguiAuto = GetObject("SAPGUI")
Else
On Error GoTo 0
End If
Set SAPguiApp = SAPguiAuto.GetScriptingEngine
End If
If Not IsObject(Connection) Then
Set Connection = SAPguiApp.OpenConnection("name string here", True)
End If
If Not IsObject(session) Then
Set session = Connection.Children(0)
End If

If session.Children.Count > 1 Then
session.findById("wnd[1]/usr/radMULTI_LOGON_OPT2").Select
session.findById("wnd[1]/usr/radMULTI_LOGON_OPT2").SetFocus
session.findById("wnd[1]/tbar[0]/btn[0]").press
End If

I have also included the portion that allows multiple sessions as it took me a REALLY long time to figure out how to do that today and it might be helpful to someone.

[daniel_mccollum]

As an aside, related to your comments around detecting multiple children.

There are methods to attach your script to an existing session, but you can also test for screen elements with something like this arrangement.

'check for "unexpected" popups i.e. test for the existence of a screen element. Wont crash the script if nothing is there. 
Do While Not session.findById("wnd[1]", False) Is Nothing
    If Not session.findById("wnd[1]/usr/radMULTI_LOGON_OPT3", False) Is Nothing Then
		'Decide what to do if there are multiple logins allowed
		if 1 = 2 then 'some meaningful if statement here, or just use this to arbitrarily decide. 
			session.findById("wnd[1]/usr/btnBUTTON_2").press
		else
			session.findById("wnd[1]/usr/btnBUTTON_3").press
		end if
    End If
	'some other test as an example for nested tests for existence. 
    If Not session.findById("wnd[1]/usr/btnSPOP-OPTION2", False) Is Nothing Then
        session.findById("wnd[1]/usr/btnSPOP-OPTION2").press
    End If
Loop
<br>

Just replace the ID's with what you need for various circumstances.

[markhamlyn]

Hi Stefan,

Thanks for the suggestion, however, our somewhat restrictive policies seem to mean that I'm not authorised to use t-code SE37. Is there anywhere else from where I could retrieve this value?

Thanks again!

Mark

[daniel_mccollum]

I have a generic session connector, however in full, it overruns the 10000 character limit for comments.

however, I have a chuck of script which will pre-check if you are already connected to the target system / client & if so use that connection instead. This feature may indirectly pre-solve your root issue.

its in the section starting with comment :'start looping at systems to connect to the correct session'

Special attention to the following since I cant include the logic for them here:

1. systemString = SAPUILandscape(system)

This calls the SAPUILandscape subroutine, this does the heavy lifting to read the SAP Landscape xml used by the logon pad itself. If you are using an ini, I have an older version somewhere. The outcome of this is a second call to...

2. SAPUILandscape = formatConnString(ConnString) This just generates the expected connection string if found.

3. Set SAPConn = SAPApp.OpenConnectionByConnectionString(systemString, True, True) Then we connect to this & attempt to fill out the login details.

'-Session Connect
'-MCCOLLUMD-20171116
' Designed to detect & connect to a target SAP connection / session
' to perform generic scripted automations
 '-Directives----------------------------------------------------------
Public Guisession As SAPFEWSELib.Guisession

'-Func Main------------------------------------------------------------
    Public Function SessConnector(system As String, clientID As String, Optional userID As String)

    '-Variables-------------------------------------------------------
        Dim SAPApp          As SAPFEWSELib.GuiApplication
        Dim SAPConnColl     As SAPFEWSELib.GuiComponentCollection
        Dim SAPConn         As SAPFEWSELib.GuiConnection
        Dim SAPSessColl     As SAPFEWSELib.GuiComponentCollection
        Dim session         As SAPFEWSELib.Guisession
        Dim SAPGuiAuto      As Object
        Dim ChildCount      As Long
        Dim arrL, arrW, i   As Long
     
        Dim vartxt          As String
        Dim answer          As String
        Dim PasswordBox     As DialogSheet
        Dim Password        As String
        Dim OutputFilePath  As String
        Dim systemString    As String
        Dim dataarray       As Variant
        Dim SAPLogonEXE     As String
     '-Error Handling Vars--------------------------------------------

    'Variables to cycle through open windows/sessions, hold connection and session info
        Dim il, it
        Dim W_Sess As Boolean
    '-Get SAP.Session------------------------------------------------
    'Set Screen Values for connection

        SAPLogonEXE = "\SAP\FrontEnd\SAPgui\SAPlogon.exe"
    'Confirm SAP System
        On Error Resume Next
        Set SAPGuiAuto = GetObject("SAPGUI")
        If SAPGuiAuto Is Nothing Then
        'Launch SAPLogon.exe
            If Environ$("ProgramW6432") Then
                OutputFilePath = Environ$("PROGRAMFILES(X86)")
            Else:
                'handle non 64 bit situations.
                OutputFilePath = Environ$("PROGRAMFILES")
            End If
            If Right(OutputFilePath, 1) <> "\" Then
                OutputFilePath = OutputFilePath & "\"
            End If
            OutputFilePath = OutputFilePath & SAPLogonEXE
            x = Shell(OutputFilePath, vbNormalFocus)
            Application.Wait Now + TimeValue("00:00:05")
            Set SAPGuiAuto = GetObject("SAPGUI")
        End If
        On Error GoTo 0
        Set SAPApp = SAPGuiAuto.GetScriptingEngine
        Set SAPConnColl = SAPApp.Connections

'start looping at systems to connect to the correct session
        For Each SAPConn In SAPConnColl
            If W_Sess = False Then


                If SAPConn.Description = VBA.Left(system, 3) Or SAPConn.Description = "" Then
                    If SAPConn.DisabledByServer = False Then
                        Set SAPSessColl = SAPConn.Sessions
                        For Each session In SAPSessColl
                            If session.Busy = False Then
                               If session.Info.SystemName = VBA.Left(system, 3) And session.Info.Client = clientID Then
                                    W_Sess = True
                                    Exit For
                               End If
                            End If
                        Next session
                    Else:
                        Call Show_Script_status("Disabled By Server")
                    End If
                End If
            ElseIf W_Sess = True Then
                Exit For
            End If
        Next SAPConn

'-No session found to connect to------------------------------------------------
        If W_Sess = False Then
            Set SAPConn = Nothing
            On Error Resume Next
            vartxt = "The Script will run on SAP System:    " + system + "   Client:   " + clientID
            answer = MsgBox(vartxt, vbOKCancel, "Confirm")
            If answer <> 1 Then
                Call Show_Script_status("Stopped")
               Exit Function
            End If

            UsePassword = True
            Set PasswordBox = Application.ThisWorkbook.DialogSheets("Dialog_logon")
            Sheet1.Activate  'Sheets("SAP GUI Scripting").Activate
            PasswordBox.EditBoxes("txtPassword").text = Password
            PasswordBox.EditBoxes("txtuserid").text = userID
            PasswordBox.Show
            Password = PasswordBox.EditBoxes("txtPassword").text
            userID = PasswordBox.EditBoxes("txtuserid").text
            If Not UsePassword Then
               Call Show_Script_status("Stopped")
               Exit Function
            End If
    
            'connect to target SAP System
            
            Set SAPConn = SAPApp.OpenConnection(system, True, True)
            
            If SAPConn Is Nothing Then
                systemString = SAPUILandscape(system)
                If systemString <> "" Then
                    Set SAPConn = SAPApp.OpenConnectionByConnectionString(systemString, True, True)
                    Application.Wait Now + TimeValue("00:00:02")
                Else:
                    Call Show_Script_status(system & " Not Found")
                    Exit Function
                End If
            End If
            
            'Fill out SAP Logon screen
            If SAPConn.DisabledByServer = False Then
                Set session = SAPConn.Sessions(0)
                session.FindById("wnd[0]").Iconify
                session.FindById("wnd[0]/usr/txtRSYST-MANDT").text = clientID
                session.FindById("wnd[0]/usr/txtRSYST-BNAME").text = userID
                session.FindById("wnd[0]/usr/pwdRSYST-BCODE").text = Password
                'Logon
                session.FindById("wnd[0]").sendVKey 0
                'check success
                Dim SapStatus As New SapStatus
                Set SapStatus = SapStatus.CatchSAPErrorMsg(session)
                If SapStatus.ErrType = "E" Then
                    Call Show_Script_status(SapStatus.ErrText)
                    session.FindById("wnd[0]").Close
                    
                    Set SAPGuiAuto = Nothing
                    Set SAPApp = Nothing
                    Set SAPConn = Nothing
                    Set session = Nothing
                    
                    Exit Function
                Else:
                
                End If
            Else:
                SAPConn.CloseConnection
            End If
         End If
        
    '-set script on correct session--------------------------------------------------
        ChildCount = SAPConn.Children.Count
        If ChildCount < 6 Then
            session.CreateSession
        End If
        Application.Wait Now + TimeValue("00:00:05")
        ChildCount = SAPConn.Children.Count
        Set session = SAPConn.Sessions(ChildCount - 1)
        session.FindById("wnd[0]").Iconify
        
    '-Run script on connected session-------------------------------------------------
'        Call Script(session)
                
    '-ending session------------------------------------------------------------------
'        Call Show_Script_status("Finished")
'Dim Guisession As New SessionConnector
            Set Me.Guisession = session
        
'        Set SAPGuiAuto = Nothing
'        Set SAPApp = Nothing
'        Set SAPConn = Nothing
'        Set session = Nothing
'-Logoff--------------------------------------------------------
    End Function

'/Main----------------------------------------------------------------

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

[stefan_schnell]

Hello Mark,

is it possible for you to use TAC SM51. Here you see Application_Server_Instance e.g. like NSP_NSP_01 and the instance number is 01.

Also is it possible that you can find the instance number in your SAP Logon. View the details of all systems and scroll to the column Instance No.

Also is it possible, as Daniel in his comment describes, to analyze the SAPLandscape.xml file. You can find the location of the file here if it is central on a server:

If it is local you can find it in the node Local Configuration Files.

Open it on the command line with the command

notepad \\YourServerPath\SAPUILandscape.xml

and search for your system.

Here you can find information like this:

<Service type="SAPGUI" uuid="19937afb-2fe4-4000-a400-d84fd6ed048a" name="NSP Test system" msid="7330022c-39c4-4d16-8356-0af4c2c1d441" server="nsp:3201" sncop="-1" sapcpg="1100" dcpg="2" mode="1"/>

The server attribute nsp:3201 contains the instance number.

As you can see, many roads leads to Rome, let us know your way.

Cheers
Stefan

[markhamlyn]

Sorry for the long delay in replying! Unfortunately, no, I can't access SM51 either! I did look around for the file you mention, it seems most of the config files are stored locally, but I can't find the specific file. However, I think I found a simple work around; I added this to the end of the logon portion of my vb script:

If session.Children.Count > 1 Then
    session.findById("wnd[1]/usr/radMULTI_LOGON_OPT2").Select
    session.findById("wnd[1]/usr/radMULTI_LOGON_OPT2").SetFocus
    session.findById("wnd[1]/tbar[0]/btn[0]").press
End If

...and that has taken care of it, now it just simulates the user option of selecting to create another session, which seems becomes the active session by default, so it works OK. The only weakness I can think of here is that if there were already the maximum number of sessions open, it would probably crash, but it's pretty unlikely that someone would leave that many sessions active, so I can live with it.

[stefan_schnell]

Hello Mark,

my suggestion is to use an alternative. Try the IP address and the system number instead the system description, e.g. like this

    '-------------------------------------------------------------------
    '- /H/ and the IP address of the system
    '- /S/ 3200 + the system number, in this example 2
    '-------------------------------------------------------------------
    Set Connection = SAPguiApp.OpenConnectionByConnectionString("/H/10.100.200.300/S/3202", True, False)

The IP address and the system number are always the same. To get the system number call FM GET_SYSTEM_NUMBER with TAC SE37.

Cheers
Stefan