排除Excel VBA与SAP的连接故障

Question

我正试图通过Excel VBA连接到SAP GUI，以便从SAP中自动提取许多数据。我看到过与连接过程中不同位置的连接错误有关的类似问题，但我没有在"；GetObject'SAPGUI'"；连接代码的一部分。

以下是我迄今为止尝试过的代码，代码注释中写有相应的错误：

Sub SAP()
If Not IsObject(application) Then
   Set SapGuiAuto = GetObject("SAPGUI") 
   Set application = SapGuiAuto.GetScriptingEngine 'This outputs "compile error: Invalid use of property"
End if
...
End Sub

我认为应用程序是VBA中的保留关键字，这是由于引用了不正确的对象而导致错误的原因。然后，我重命名为一个唯一的变量名"Sapplication"，在另一个位置触发了一个错误：

Sub SAP()
If Not IsObject(sapplication) Then
   Set SapGuiAuto = GetObject("SAPGUI") 'This now outputs "Automation Error, Invalid syntax -2147221020"
   Set sapplication = SapGuiAuto.GetScriptingEngine 
End if
...
End Sub

根据SAP论坛的建议，建议我将GetObject("SAPGUI"(替换为CreateObject("SAP GUI.Application"(，这会提示一个新错误：

Sub SAP()
If Not IsObject(sapplication) Then
   Set SapGuiAuto = CreateObject("SAPGUI.Application") 
   Set sapplication = SapGuiAuto.GetScriptingEngine 'Object doesn't support this method or property (438)
End if
...
End Sub

我不确定这段代码哪里出了问题，对于其他人从SAP中记录的宏中获得的内容，原始代码似乎相当标准。关于我可能在哪里出错，有什么想法或建议吗？

更新/编辑：在新的SAP窗口和Excel启动后，我能够成功地在代码中取得更大进展。然而，我遇到了一个新的问题，即似乎没有创建连接对象(或者至少与我可以引用的连接对象相关的实例(：

Set rotEntry =GetObject("SAPGUI")
Set sapplication = rotEntry.GetScriptingEngine 'I did confirm that use of 'application is conflicting per the SAP GUI documentation, so replaced with sapplication.
Set Connection = sApplication.children(0) 'Error: The enumerator of the collection cannot find an element with specified index

Answer 1

我发现有时我需要尝试一个稍微不同的登录脚本，比如：

If Not IsObject(SapApp) Then
    On Error Resume Next
    Set SapGuiAuto = CreateObject("SAPGUI")
    If SapGuiAuto Is Nothing Then
        Err.Clear
        Set SapGuiAuto = GetObject("SAPGUISERVER")
    End If
    On Error GoTo 0
    
    Set SapApp = SapGuiAuto.GetScriptingEngine    'Object doesn't support this method or property (438)
End If

Answer 2

经过大量的尝试和错误，我发现通过shell命令打开而不是当前打开GUI是成功的。我已经发布了目前为我工作的代码如下：

Sub sap()

Dim sapgui
Dim applic
Dim connection
Dim session
Dim wshshell
Shell "C:Program Files (x86)SAPFrontEndSapGuisaplogon.exe", vbNormalFocus 'OPEN SAP LOGON PAD 'edit to desired path
Set wshshell = CreateObject("WScript.shell")
Do Until wshshell.AppActivate("SAP Logon")
Application.Wait Now + TimeValue("0:00:02") 'set a delay timer to ensure the shell logon is ready
Loop
Set wshshell = Nothing
Set sapgui = GetObject("SAPGUI")
Set applic = sapgui.Getscriptingengine
Set connection = applic.OpenConnection("PA4 - Production North America ERP", True) 'edit this to reach the desired section within SAP
Set session = connection.Children(0)
'INSERT AUTOMATED MARCO HERE
End sub