Question Re: Excel VBA Single Sign On script in Additional Q&A

Excel VBA Single Sign On script

Former Member — Wed, 18 May 2016 21:32:47 GMT

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

Re: Excel VBA Single Sign On script

script_man — Wed, 25 May 2016 12:48:57 GMT

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

Re: Excel VBA Single Sign On script

Former Member — Thu, 26 May 2016 15:06:42 GMT

Hi Script Man,

I've found that thread in previous searching, but it never resolved the problem. I can get it to launch my SAP Logon but never get it to select an environment to connect to. I receive the following when I attempt to appropriate your code:

I think what I need is a way to determine how to insert a string into this field and send a carriage return - that's it. One of your commented lines of code, such as session.findByID("wnd[0]/usr/txtRSYST-LANGU").Text = "EN" for example. If I could figure out what the ID is for the below field I could get this thing to do what I need it to.

The issue being I have no idea how to identify what that points to, as I can't run a script recording from this window and I've been completely reliant on that tool up until now. Nor does using F1 seem to return any viable information on the field name like it would inside the SAP GUI normally. Do you know of a way to find out how to reference that input field for a string insert?

Thanks,

Jon

Re: Excel VBA Single Sign On script

Former Member — Thu, 26 May 2016 15:17:30 GMT

Update - I've attempted to use the NwbcPropertyCollector.exe tool to read the field, but it doesn't recognize the SAP Logon Launcher as a valid session.

Re: Excel VBA Single Sign On script

script_man — Thu, 26 May 2016 16:43:27 GMT

How about the following system name?

set Connection = SAP_applic.openconnection("Prod Systems Single Sign On")

Regards,

ScriptMan

Re: Excel VBA Single Sign On script

Former Member — Thu, 26 May 2016 17:04:10 GMT

Hi ScriptMan,

No luck, but thanks for your continued attempts to help. I tried to send the enter key via Excel VBA with the following line:

Application.SendKeys ("{ENTER}")

While the SAP Logon screen was activated with PRD selected manually - to see if it would even accept an input. Do we know if the SAP Logon launcher even has an Interface that Excel can talk to? Or are we attempting to use a different function with your code?

Thanks,

Jon

Re: Excel VBA Single Sign On script

script_man — Fri, 27 May 2016 05:53:53 GMT

Hi Jon,

as I said I have no experience with SSO. But if you feel like it, you can view the following links. Maybe this can give you an important suggestion.

Connect via RFC from VB with SSO | SCN

http://stackoverflow.com/questions/37236396/vba-excel-sso-to-sap-runtime-error-70-access-denied/37372388#37372388

My recommendation: Do not continue please the way through the SendKeys method. This will not lead to the goal.

Regards,

ScriptMan

Re: Excel VBA Single Sign On script

markhamlyn — Thu, 28 Jul 2016 18:05:29 GMT

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.

Re: Excel VBA Single Sign On script

Former Member — Thu, 01 Sep 2016 16:21:49 GMT

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"