excel - Exported file opens after macro completes - unwanted

Question

I have some VBA code that exports SAP data to a .XLSX file, then imports this file into excel. After the file is imported, I have code that performs many other actions (eg parses and organizes data, counts cells, etc). After the macro finishes, the exported SAP .XLSX file opens up and I'd like it not to.

I've tried using the Kill function, which it does successfully, but at the end of the macro, instead of opening the file, it has a message about the file not found, which makes sense.

I've tried closing the file before the macro completes, but this also results in error since the file isn't actually open.

I've tried opening and then closing the file before the macro completes, it does this successfully, but then again at the end of the macro, the file opens.

BUT, when I break the code and step through, then the file does not open at the end.

I've tried a few other various timer functions, DoEvents, and such, and still no luck.

Does anyone have any ideas on how to either: 1. Prevent the exported file from opening at the end of the code or 2. Prevent the "file not found" message from showing up so the file can be killed during the macro.

Sample code

'Opens SAP connection
Dim SAPGUI
Dim Applic
Dim connection
Dim Session
Dim WSHShell

Application.DisplayAlerts = False

Shell "C:Program Files (x86)SAPFrontEndSAPguisaplogon.exe", vbNormalFocus

Set WSHShell = CreateObject("WScript.Shell")
  Do Until WSHShell.AppActivate("SAP Logon ")
    Application.Wait Now + TimeValue("0:00:01")
  Loop

Set WSHShell = Nothing
Set SAPGUI = GetObject("SAPGUI")
Set Applic = SAPGUI.GetScriptingEngine
Set connection = Applic.OpenConnection("04. R3 PRD []", True)
Set Session = connection.Children(0)
'  Session.findById("wnd[0]").iconify
  Session.findById("wnd[0]/usr/txtRSYST-MANDT").Text = "100"
  Session.findById("wnd[0]/usr/txtRSYST-BNAME").Text = Environ("Username")
  Session.findById("wnd[0]/usr/pwdRSYST-BCODE").Text = sysstart.psswrd
  Session.findById("wnd[0]").sendVKey 0

Session.findById("wnd[0]").maximize
Session.findById("wnd[0]/tbar[0]/okcd").Text = "/nZUI_SELECTION"
Session.findById("wnd[0]").sendVKey 0

''Performs some filters and opens data 

' Export from SAP to .xlsx file.
Session.findById("wnd[0]/usr/cntlGRID1/shellcont/shell").contextMenu
Session.findById("wnd[0]/usr/cntlGRID1/shellcont/shell").selectContextMenuItem "&XXL"
Session.findById("wnd[1]/tbar[0]/btn[0]").press
Session.findById("wnd[1]/usr/ctxt[0]").Text = "C:Users" & Environ("Username") & "Downloads"
Session.findById("wnd[1]/usr/ctxt[1]").Text = "SAP_export.XLSX"
Session.findById("wnd[1]/tbar[0]/btn[11]").press

'Closes SAP connection
Set Session = Nothing
  connection.CloseSession ("ses[0]")
Set connection = Nothing


'Clear table from SMS Input
    ThisWorkbook.Worksheets("SMS Input").Select
    Cells.Select
    Selection.ClearContents

'Insert .xlsx file data
    Cells.Select
    Selection.Delete Shift:=xlUp
    Range("A6").Select
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _
        "OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Password="""";User ID=Admin;Data Source=C:Users" & Environ("Username") & "DownloadsSAP_export.XLSX;Mode=Share Deny None" _
        , _
        ";Extended Properties=""HDR=YES;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";Jet OL" _
        , _
        "EDB:Engine Type=37;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Je" _
        , _
        "t OLEDB:New Database Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Lo" _
        , _
        "cale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False;Je" _
        , "t OLEDB:Bypass UserInfo Validation=False"), Destination:=Range("$A$6")). _
        QueryTable
        .CommandType = xlCmdTable
        .CommandText = Array("Sheet1$")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .SourceDataFile = "C:Users" & Environ("Username") & "DownloadsSAP_export.XLSX"
        .ListObject.DisplayName = "Table_export3"
        .Refresh BackgroundQuery:=False
        .MaintainConnection = False
    End With

After the file is imported, I parse through some columns and organize the information across the worksheets.

At the end of the code, I have tried different variations as mentioned above.

'Open the export and then close to avoid it opening at end of macro.
Workbooks.Open Filename:="C:Users" & Environ("Username") & _
                          "DownloadsSAP_export.XLSX"
Workbooks("SAP_export.XLSX").Close savechanges:=False
Application.Wait Now + TimeValue("0:00:01")
Kill "C:Users" & Environ("Username") & "DownloadsSAP_export.XLSX"

At first, I thought this was an issue with the section where it imports the .XLSX using OLEDB but it isn't. If I End the code after the SAP connection close, the file will automatically open at the end. I've seen other posts with this same issue but no solid answers. Hopefully this is clear...

Thanks in advance for any feedback,

Answer 1

Further Suggestion:

At the end of your VBA program (without changes) , run the script below.

For example:

. . .
'Open the export and then close to avoid it opening at end of macro.
set Wshell = CreateObject("WScript.Shell")
Wshell.run "c:mpSAP_Workbook_Close.vbs",1,false
End Sub

SAP_Workbook_Close.vbs:

SAP_Workbook = "SAP_export.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

Regards, ScriptMan