I am trying to optimize my Excel VBA to SAP connection and don't want to click "OK" on two message boxes that appear when starting the following code:
Sub SAP_1() Dim obj_Shell As Object Dim obj_SAPGUI As Object Dim obj_Application As Object Dim obj_Connection As Object Dim obj_session As Object Application.DisplayAlerts = False Shell "C:\Program Files (x86)\SAP\FrontEnd\SAPgui\saplogon.exe", 4 Set obj_Shell = CreateObject("WScript.Shell") Do Until obj_Shell.AppActivate("SAP Logon") application.Wait Now + TimeValue("0:00:01") Loop Set obj_Shell = Nothing Set obj_SAPGUI = GetObject("SAPGUI") Set obj_Application = obj_SAPGUI.GetScriptingEngine Set obj_Connection = obj_Application.OpenConnection(str_ConName, True) Set obj_session = obj_Connection.Children(0) ' rest of the code Application.DisplayAlerts = True End Sub How can I avoid the following SAP message boxes or click them via VBA:
At line Set obj_Application = obj_SAPGUI.GetScriptingEngine:
"A script is attempting to access SAP GUI"
At line Set obj_Connection = obj_Application.OpenConnection(str_ConName, True):
"A script is opening a connection to system: *******"
What's the difference to the code below? Why is the SAP GUI Scripting asking not to define them as Objects? Is there a better alternative?
If Not IsObject(obj_SAPGUI) Then Set obj_SAPGUI = GetObject("SAPGUI") Set obj_Application = obj_SAPGUI.GetScriptingEngine End If If Not IsObject(obj_Connection) Then Set obj_Connection = obj_Application.Children(0) End If If Not IsObject(obj_session) Then Set obj_session = obj_Connection.Children(0) End If If IsObject(obj_WScript) Then obj_WScript.ConnectObject obj_session, "on" obj_WScript.ConnectObject obj_Application, "on" End If Are there other things in the code that can be optimized?
Thank you for your help.
52 Answers
In order to avoid the messages that a script tires to access resp. connect to the SAPGUI you have to change settings either in the registry or via SAPGUI.
In the SAPGUI press Alt-F12 and then select Options, goto Scripting, and uncheck all check boxes below Enable scripting.
These settings are stored in the registy and one could also use VBA code to set them. The key is HKEY_CURRENT_USER\Software\SAP\SAPGUI Front\SAP Frontend Server\Security\
Thank you very much, that's my final code right now:
Function RegKeyExists(i_RegKey As String) As Boolean Dim myWS As Object Set myWS = CreateObject("WScript.Shell") On Error GoTo ErrorHandler myWS.RegRead i_RegKey RegKeyExists = True Exit Function ErrorHandler: RegKeyExists = False End Function ' '----------------------------------------------------------------------- Sub RegKeyReset() Dim obj_WS As Object Dim RegKey1 As String Dim RegKey2 As String Dim RegKey3 As String Set obj_WS = CreateObject("WScript.Shell") RegKey1 = "HKEY_CURRENT_USER\Software\SAP\SAPGUI Front\SAP Frontend Server\Security\UserScripting" RegKey2 = "HKEY_CURRENT_USER\Software\SAP\SAPGUI Front\SAP Frontend Server\Security\WarnOnAttach" RegKey3 = "HKEY_CURRENT_USER\Software\SAP\SAPGUI Front\SAP Frontend Server\Security\WarnOnConnection" ' RegKey1 If RegKeyExists(RegKey1) = False Then Exit Sub Else obj_WS.RegWrite RegKey1, 1, "REG_DWORD" ' Value = 1, Type = Boolean End If ' RegKey2 If RegKeyExists(RegKey2) = False Then Exit Sub Else obj_WS.RegWrite RegKey2, 0, "REG_DWORD" ' Value = 0, Type = Boolean End If ' RegKey3 If RegKeyExists(RegKey3) = False Then Exit Sub Else obj_WS.RegWrite RegKey3, 0, "REG_DWORD" ' Value = 0, Type = Boolean End If End Sub ' '----------------------------------------------------------------------- Sub SAPTransaction() Dim ... Set ... Call RegKeyReset ' <--------------------------- Problem solved here... ' rest of the code End Sub ' I did it this way, because I wont be the only person/user to use the macro, so I don't have to tell everybody to change their settings in SAP.
1
