SAP GUI script from Excel VBA Macro optimizing-CodePudding

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
    '
    ' first message box from SAP:
    ' "A script tries to access SAP"
    ' 
    Set obj_Connection = obj_Application.OpenConnection(str_ConnectionName, True)
    '
    ' second message box from SAP:
    ' "A script opens a connection to the following system: ..."
    '
    Set obj_session = obj_Connection.Children(0)

' rest of the code
application.DisplayAlerts = True
End Sub

How can I avoid those message boxes or click them via VBA?

And what's the differents to the code below? Why is the SAP GUI Scripting asking not to define them as Objects? Is this 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.

CodePudding user response：

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\

CodePudding user response：

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    ' <--------------------------- Thank You !!!

' 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.

Also thanks to: https://www.slipstick.com/developer/read-and-change-a-registry-key-using-vba/