Problem closing Excel exported from SAP GUI with VBA

Question

I run a macro which download an Excel report from SAP GUI, save it, and then I need the Excel to be closed. I read similar post in this page but none of them solves my issue.

The problem is that once the Macro finish, the Excel downloaded from SAP GUI automatically opens. Is like the Excel is running in the background. So, if I close it during the macro, the code won't find the open Excel (which make sense since the excel is not opened yet) and gives error.. BUT when the macro finish the excel appears (that's because of SAP software behavior, that when you download an Spreadsheet the Excel opens automatically. I have tried changing SAP configuration but didn't get lucky)

I tried put Application.Wait, Workbook.Close, kill the workbook (which it gives a problem because a pop up shows telling that the workbook does not exist), call another sub, etc.

I tried a solution that I read in this page which is add the wshell part but doesn't work in my code.

VBA code in TestExcel.xlsm:

        Sub test

        'Sap conection

        Application.DisplayAlerts = False
        Application.ScreenUpdating = False

        session.findById("wnd[0]").maximize
        session.findById("wnd[0]/tbar[0]/okcd").Text = "/nzv04hn"
        session.findById("wnd[0]").SendVKey 0
        session.findById("wnd[0]/usr/ctxtS_VKORG-LOW").Text = "CR01"
        session.findById("wnd[0]/usr/ctxtS_AUART-LOW").Text = "zor"
        session.findById("wnd[0]/usr/chkP_PGI").SetFocus
        session.findById("wnd[0]").SendVKey 8
        session.findById("wnd[1]/tbar[0]/btn[0]").press
        session.findById("wnd[0]").SendVKey 43
        session.findById("wnd[1]/usr/ctxtDY_PATH").Text = "C:\Users\xxx\Desktop\"
        session.findById("wnd[1]/usr/ctxtDY_FILENAME").Text = "CR01ZV04HN.XLSX"
        session.findById("wnd[1]/usr/ctxtDY_FILENAME").caretPosition = 4
        session.findById("wnd[1]/tbar[0]/btn[7]").press
        
        Application.DisplayAlerts = True
        Application.ScreenUpdating = True
        
        Set Wshell = CreateObject("WScript.Shell")
        Wshell.Run "C:\Users\Desktop\xxx\SAP_Workbook_Close.vbs", 1, False
        End sub

VBScript code in SAP_Workbook_Close.vbs:

SAP_Workbook = "CR01ZV04HN.XLSX" 
on error resume next
do
err.clear
Set xclApp = GetObject(, "Excel.Application")
If Err.Number = 0 Then exit do
'msgbox "Wait for Excel session"
wscript.sleep 2000
loop

do 
err.clear
Set xclwbk = xclApp.Workbooks.Item(SAP_Workbook)
If Err.Number = 0 Then exit do
'msgbox "Wait for SAP workbook"
wscript.sleep 2000
loop

on error goto 0 
Set xclSheet = xclwbk.Worksheets(1)

xclApp.Visible = True
xclapp.DisplayAlerts = false

xclapp.ActiveWorkbook.Close


Set xclwbk = Nothing
Set xclsheet = Nothing
'xclapp.Quit
set xclapp = Nothing

Thanks in advance for the help.

Try this in your original workbook: `Application.DisplayAlerts = False : Application.Quit` — AAA, Jul 26 '19 at 15:16
May Try `xclapp.Quit` after line `xclapp.ActiveWorkbook.Close` — Ahmed AU, Jul 27 '19 at 03:42
To see how far your program comes, please activate the following commands in the script: msgbox "Wait for Excel session" and msgbox "Wait for SAP workbook" — ScriptMan, Jul 30 '19 at 11:56
What happens if you explicitly open the workbook in your code and the close it? — Nacorid, Jul 30 '19 at 13:59

Problem closing Excel exported from SAP GUI with VBA

0 Answers0