Skip to Content
avatar image
Former Member

Excel VBA Single Sign On script

Hi folks,

I've been searching these forums with not much luck - most scripts seem to be using a form of connection with SAP that involves hard coding one's username and password, either into the excel spreadsheet or into the VBA itself. I'm trying to find some sort of way to utilize the "SAP Logon 740" launcher that is prompting me to input an environment (think "PRD" or "QRA"), which will then either automatically roll out the SAP session if only one client exists for that environment, or prompt another selection for the client within an SAP session. As this second step is inside an active SAP session, I've been able to record and run a script to select the proper client. I just need to be able to utilize single sign on from the launcher to kick off a session for a given environment - it's really just highlighting one text search field and inputing a passed string variable.

Launching the application isn't a problem, but since I cannot figure out a way to record a macro with the launcher itself I can't get from point A (launching the application) to point C (being able to execute my macros). Point B, navigating to the proper environment/client, is not something I'm able to find good logic for.

Appreciate your help experts - this is important as our sessions seem to get bogged down after running macros for 40-50 iterations. Whether that's due to behind the scenes transaction tracking or something else, we find that refreshing the session manually every 10-15 minutes significantly speeds up our net processing. We're hoping to automate this refreshing process so this will be a large upgrade to our overall macro performance. Appreciate urgent guidance on this and I will do my best to be timely in my response to assistance.

Thanks,

Jon

Capture.JPG (27.5 kB)
Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

2 Answers

  • Best Answer
    May 25, 2016 at 12:48 PM

    Hi Jon,

    welcome to the forum. The problems with the speed during the execution of macros come very familiar to me. We do not work with single sign on. Therefore, coming from me only a suggestion of how to realize it. Please see this link:

    The following commands, you can ignore likely:

    session.findById("wnd[0]/usr/txtRSYST-MANDT").Text = "myMandant"

    session.findById("wnd[0]/usr/txtRSYST-BNAME").Text = "myName"

    session.findById("wnd[0]/usr/pwdRSYST-BCODE").Text = "myPW"

    session.findById("wnd[0]/usr/txtRSYST-LANGU").Text = "EN"

    session.findById("wnd[0]").sendVKey 0


    Regards,

    ScriptMan

    Add comment
    10|10000 characters needed characters exceeded

    • Hi there,

      I've been searching pretty extensively this morning and this is among the related posts I have found. Last night we upgraded our SAP GUI from 7.20 (I think, not sure of the version etc.) to 7.40 (7400.2.7.1112, running on Windows 7 Enterprise 6.1 (7601)). Prior to last night, I had an Excel sheet that ran a VB script every night on a timer that connected to SAP, copied some data to a sheet, then closed and wait for the next night to run again. Since the update, it no longer works.

      The basic part of the script that (used to) make the connection to SAP is:

      Sub SAP_Connect_basic()

      If Not IsObject(SAPguiApp) Then

      Set SAPguiApp = CreateObject("Sapgui.ScriptingCtrl.1")

      End If

      If Not IsObject(Connection) Then

      Set Connection = SAPguiApp.OpenConnection("<text string in Logon pad describing the connection>", True)

      End If

      If Not IsObject(Session) Then

      Set Session = Connection.Children(0)

      End If

      End Sub

      If you run the script once, you get popup from MS Visual Basic:


      Run-time error '1000':

      SAP Logon connection entry not found.


      If you terminate it and run it a second time, you get a new error:

      Run-time error '601':

      Sapgui cannot be initialized.

      I’m not sure what is happening, it was working fine until the update (we are using SSO, so no need for userID or password), the ONLY change I can see is a change in the text description of the connection in the “System Connection Parameters” box, but I have tried both the “new” and “old” descriptions and both produce the same error.

      I’m stumped! We use this data top provide some performance metrics, I’m hesitant to go back to the “old” way of doing it (via manual dumps to Excel) as it is time consuming and inefficient.

      Any help would be greatly appreciated!

      Thanks,

      Mark

      PS we are running MS Office Professional Plus 2010, in case that matters.

  • avatar image
    Former Member
    Sep 01, 2016 at 04:21 PM

    Hi Jonathan -

    I have a SSO solution I created that has worked for me. It opens your SAP connection via the Logon Pad (thus allowing SSO to do it's work).

    First it checks through Shell to see if the Logon Pad has been launched, and launches if it is hasn't.

    It then strikes ENTER to open the top SAP connection.

    Finally, this sample will start transaction VA03 (for me, that is Read-Only Orders) and Echo a confirmation that it has started.

    The key caveat here is that I have my SAP connections saved to my Favorites folder, which by default is loaded on the Logon Pad. Then, I only need to use the top connection. If you are able to move your two to favorites, and know that they will always be in the same position, you can sendkeys to select them depending on which instance you need to run. (DOWN DOWN DOWN ENTER, for example).

    Probably not the most efficient way, but it works 100% of the time for me!

    Good luck

    Joe

    On Error Resume Next

    If Not IsObject(application) Then

    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 740")

    WScript.Sleep 500

    Loop

    WshShell.sendkeys "~"

    Set SapGuiAuto = GetObject("SAPGUI")

    Set application = SapGuiAuto.GetScriptingEngine

    Do While Not wshShell.AppActivate("SAP Easy Access")

    WScript.Sleep 500

    Loop

    Else

    On Error Goto 0

    End If

    Set application = SapGuiAuto.GetScriptingEngine

    End If

    On Error Resume Next

    If Not IsObject(connection) Then

    Set connection = application.Children(0)

    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 740")

    WScript.Sleep 500

    Loop

    WshShell.sendkeys "~"

    Set SapGuiAuto = GetObject("SAPGUI")

    Set application = SapGuiAuto.GetScriptingEngine

    Do While Not wshShell.AppActivate("SAP Easy Access")

    WScript.Sleep 500

    Loop

    Else

    On Error Goto 0

    End If

    Set connection = application.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 application, "on"

    End If

    session.startTransaction "VA03"

    WScript.Echo "VA03 Started"

    Add comment
    10|10000 characters needed characters exceeded