Exported file opens after macro completes - unwant

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)\SAP\FrontEnd\SAPgui\saplogon.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") & "\Downloads\SAP_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") & "\Downloads\SAP_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") & _
                          "\Downloads\SAP_export.XLSX"
Workbooks("SAP_export.XLSX").Close savechanges:=False
Application.Wait Now + TimeValue("0:00:01")
Kill "C:\Users\" & Environ("Username") & "\Downloads\SAP_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

I have tried to do what I explained in the comments. It isn't very neat and I wasn't able to test. I only changed a couple of lines to tidy a couple of things up, I dont think that line is needed in the Insert .xlsx from file data section thought. Let me know how you go.

Sub OriginalSub()
  'Opens SAP connection
  [snip as no change]

  'Closes SAP connection
  [snip as no change]

  Call ImportData()

  Kill "C:\Users\" & Environ("Username") & "\Downloads\SAP_export.XLSX"

End Sub


Sub ImportData()

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

'Insert .xlsx file data
    ThisWorkbook.Worksheets("SMS Input").Range("A6").Select
    [snip as no changes from here by me]

    Workbooks("SAP_export.XLSX").Close savechanges:=False

End Sub

Answer 2

I am trying to write a little more today so that my answer will not be deleted. ;-)

I offer the following workaround for the issue:

'old codes
. . .
Session.findById("wnd[1]/usr/ctxt[1]").Text = "SAP_export.XLSX"
Session.findById("wnd[1]/tbar[0]/btn[11]").press
'new codes
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
 session.findById("wnd[0]").iconify
 session.findById("wnd[0]").maximize
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

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

Regards, ScriptMan

Answer 3

My solution here if anybody still needs help. I created a sub in order to kill a lot of files i export. thx scriptman ...

    .findById("wnd[0]/mbar/menu[0]/menu[10]/menu[3]/menu[1]").Select
    .findById("wnd[1]/usr/ctxtDY_PATH").Text = "C:\Informe\"
    .findById("wnd[1]/usr/ctxtDY_FILENAME").Text = "tcurr.xlsx"
    .findById("wnd[1]/usr/ctxtDY_FILENAME").caretPosition = 10
    .findById("wnd[1]/tbar[0]/btn[11]").press
    End With

    'Application.Wait Now + TimeValue("00:00:02")

    Call Esperar("tcurr.XLSX")

   Public Sub Esperar(Optional ByVal archivo As String)    
   On Error Resume Next    
   Set Wshell = CreateObject("WScript.Shell")    
   Do    
    Err.Clear
    Set xclapp = GetObject(, "Excel.Application")
    If Err.Number = 0 Then Exit Do
    DoEvents '---> This do the trick in VB7
    'msgbox "Wait for Excel session"

    wscript.Sleep 2000
    Loop

   Do
    Err.Clear
    Set xclwbk = xclapp.Workbooks.Item(archivo)
    If Err.Number = 0 Then Exit Do
    DoEvents
    '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
   End Sub

Answer 4

So I have been reading a lot about this issue and a lot of people wondering why... i finally got to the point!! Is a thing in SAP Configuration. Go Check on top right corner... click on the TV > Settings > Interactive > Quick Info and use accesibility mode. There it is! Set it to None! and you are done... I got this from a user that told me that a lot of books opened at the end.

I wrote a small program to solved this issue, if anybody check on this let me know:

   Public Sub ConfSAP()

   Set objShell = CreateObject("WScript.Shell")

   reg = "REG_DWORD"



    strRoot_16 = "HKEY_CURRENT_USER\SOFTWARE\SAP\SAPGUI Front\SAP Frontend Server\Customize\BubbleDelay"
strRoot_17 = "HKEY_CURRENT_USER\SOFTWARE\SAP\General\AccMode"
            strModify = objShell.RegWrite(strRoot_16, "00000003", reg)
   strModify = objShell.RegWrite(strRoot_17, "On", "REG_SZ")
                strModify = Null
                strRoot_16 = Null

strRoot_17 = Null

    subfolder = Null 
    reg = Null Set 
    objShell = Nothing 
    End Sub

Answer 5

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:\tmp\SAP_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