SAP GUI VBScript for data export results in empty document when run in VBA [excel]

Question

I have a VBScript that runs a data export from SAP GUI. If executed alone or in SAP it runs and results in an excel worksheet with the desired data.

However, I want to have it implemented in a VBA Sub. I found this thread that was already very helpful. When I start the macro in excel, it runs the whole script through SAP without error only to open the destination Excel file without saving the data to it. At first, I didn't realize it, but when I clear the document it is obvious that it is not being overwritten.

Also there is no error message showing.

Thanks in advance for your help.

Public Sub Connect_To_SAP()
  
On Error GoTo Err_NoSAP

If Not IsObject(SAPGuiApp) Then
   Set SapGuiAuto = GetObject("SAPGUI")
   Set SAPGuiApp = SapGuiAuto.GetScriptingEngine
End If
If Not IsObject(Connection) Then
   Set Connection = SAPGuiApp.Children(0)
End If
If Not IsObject(SAP_session) Then
   Set SAP_session = Connection.Children(0)
End If
If IsObject(WScript) Then
   WScript.ConnectObject SAP_session, "on"
   WScript.ConnectObject SAPGuiApp, "on"
End If

If (Connection.Children.Count > 1) Then GoTo Err_TooManySAP

Set aw = SAP_session.ActiveWindow()
aw.findById("wnd[0]").Maximize

On Error GoTo Err_Description
SAP_session.findById("wnd[0]").Maximize
SAP_session.findById("wnd[0]/tbar[0]/btn[12]").press
SAP_session.findById("wnd[0]/tbar[0]/btn[12]").press
SAP_session.findById("wnd[0]/tbar[0]/btn[12]").press
SAP_session.findById("wnd[0]/tbar[0]/okcd").Text = "Execution"
SAP_session.findById("wnd[0]/tbar[0]/btn[0]").press
SAP_session.findById("wnd[0]/tbar[1]/btn[8]").press
SAP_session.findById("wnd[0]/usr/cntlGRID1/shellcont/shell").setCurrentCell -1, ""
SAP_session.findById("wnd[0]/usr/cntlGRID1/shellcont/shell").SelectAll
SAP_session.findById("wnd[0]/usr/cntlGRID1/shellcont/shell").contextMenu
SAP_session.findById("wnd[0]/usr/cntlGRID1/shellcont/shell").selectContextMenuItem "&XXL"
SAP_session.findById("wnd[1]/tbar[0]/btn[0]").press
SAP_session.findById("wnd[1]/usr/ctxtDY_PATH").Text = "C:\Users\%UserName%\Documents\SAP\SAP GUI\"
SAP_session.findById("wnd[1]/usr/ctxtDY_FILENAME").Text = "Test.XLSX"
SAP_session.findById("wnd[1]/usr/ctxtDY_FILENAME").caretPosition = 8
SAP_session.findById("wnd[1]/tbar[0]/btn[11]").press

Exit Sub

Err_Description:
    MsgBox ("The program has generated an error;" & Chr(13) & _
    "the reason for this error is unknown."), vbInformation
        Exit Sub

Err_NoSAP:
MsgBox ("You don't have SAP open or " & Chr(13) & _
"scripting has been disabled."), vbInformation
        Exit Sub

Err_TooManySAP:
MsgBox ("You must only have one SAP session open. " & Chr(13) & _
        "Please close all other open SAP sessions."), vbInformation
         Exit Sub


End Sub

Edit:Typos

score 0 · Accepted Answer · answered Nov 09 '21 at 13:08

0

By chance I ran into the same mistake with different consequences elsewhere. Long story short: It seems you can't use %UserName% when operating from Excel. Instead replaced it with this

SAP_session.findById("wnd[1]/usr/ctxtDY_PATH").Text = "C:\Users\" & Environ("username") & "\Documents\SAP\SAP GUI\"

Might help someone.

answered Nov 09 '21 at 13:08

Robin

11
4

Not sure how that would be any different for VBScript, you would have had the same issue. – user692942 Nov 09 '21 at 15:52
You are right, I had a lot of versions and apperently tested only VBScript only with the one with the original reference (my username entered). Wasn't helping to find the problem and could be misleading here. So yes, only the VBScript session from above should have the same problem. – Robin Nov 11 '21 at 06:29

SAP GUI VBScript for data export results in empty document when run in VBA [excel]

1 Answers1