SAP GUI script opens an Excel window and I can't stop it

Question

I'm trying to automate SAP GUI export to an Excel file, which in turn is then used by another Excel file with some VBA code to automate filtering and formatting the data.

I've got it all running, except for one (seemingly minor) problem: SAP GUI always opens the exported Excel file automatically and there seems to be no way of stopping this as it seems to happen AFTER the subroutines to import the Data in Excel VBA have finished (they contain the SAP GUI script).

If I only run the sap_export subroutine, then the Excel file opens, which is simply annoying. But if I run refresh_sap(), which calls sap_export(), followed by refresh(), which accesses the exported Excel file importing data, I get the prompt telling me that the file is already in use.

I have found no way to stop the file from being opened by the SAP GUI script as it does not seem to happen during run time. I suspect that this is why I couldn't find any way to use Application.Wait or DoEvents to solve this. However long I wait, it will not work, as the file is simply never open until after runtime.

Sub refresh_sap()       
    Call sap_export
    Call refresh
End Sub

Sub refresh()
    
    'refreshes the connection to the SAP-exported Excel-file

    ActiveWorkbook.Connections("export").refresh
    
    'deleting unwanted data

    ActiveWorkbook.Sheets("PC-Liste komplett").Select
    Selection.AutoFilter
    ActiveSheet.ListObjects("Tabelle_export").Range.AutoFilter Field:=4, Criteria1:="Löschen"
    Range("A2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.EntireRow.Delete
    ActiveSheet.ListObjects("Tabelle_export").Range.AutoFilter Field:=4
    Range("A1").Select   
End Sub

Sub sap_export()

    Dim set0 As Integer
    Dim set1 As String
    Dim set2 As Boolean

    'vbs-script recorded with the SAP-GUI

    If Not IsObject(sapp) Then
       Set SapGuiAuto = GetObject("SAPGUI")
       Set sapp = SapGuiAuto.GetScriptingEngine
    End If
    If Not IsObject(Scon) Then
       Set Scon = sapp.Children(0)
    End If
    If Not IsObject(session) Then
       Set session = Scon.Children(0)
    End If
    If IsObject(WScript) Then
       WScript.connectobject session, "on"
       WScript.connectobject sapp, "on"
    End If
    
    session.findById("wnd[0]/tbar[0]/okcd").Text = "/n KE5X"
    session.findById("wnd[0]").sendVKey 0
    session.findById("wnd[0]/usr/ctxtGT_PRCTR-LOW").Text = "*"
    session.findById("wnd[0]").sendVKey 8        
    session.findById("wnd[0]/usr/cntlGRID1/shellcont/shell/shellcont[1]/shell").contextMenu
    session.findById("wnd[0]/usr/cntlGRID1/shellcont/shell/shellcont[1]/shell").selectContextMenuItem "&XXL"
    
    If session.findById("wnd[1]/usr/radRB_1").Selected = True Then
        set0 = 0
    ElseIf session.findById("wnd[1]/usr/radRB_2").Selected = True Then
        set0 = 1
    ElseIf session.findById("wnd[1]/usr/radRB_OTHERS").Selected = True Then
        set0 = 2
    End If
    
    set1 = session.findById("wnd[1]/usr/cmbG_LISTBOX").Key
    set2 = session.findById("wnd[1]/usr/chkCB_ALWAYS").Selected        
    session.findById("wnd[1]/usr/radRB_OTHERS").Select
    session.findById("wnd[1]/usr/cmbG_LISTBOX").Key = "10"
    session.findById("wnd[1]/usr/chkCB_ALWAYS").Selected = False        
    session.findById("wnd[1]").sendVKey 0
    session.findById("wnd[1]/usr/ctxtDY_PATH").Text = "S:\FIN-Alle\Kostenstellen - Innenauftragsliste\SAP"
    session.findById("wnd[1]/tbar[0]/btn[11]").press    
    session.findById("wnd[0]/usr/cntlGRID1/shellcont/shell/shellcont[1]/shell").contextMenu
    session.findById("wnd[0]/usr/cntlGRID1/shellcont/shell/shellcont[1]/shell").selectContextMenuItem "&XXL"

    Select Case set0
        Case 0
        session.findById("wnd[1]/usr/radRB_1").Select
        Case 1
        session.findById("wnd[1]/usr/radRB_2").Select
        Case 2
        session.findById("wnd[1]/usr/radRB_OTHERS").Select
    End Select
    
    session.findById("wnd[1]/usr/cmbG_LISTBOX").Key = set1
    session.findById("wnd[1]/usr/chkCB_ALWAYS").Selected = set2
    session.findById("wnd[1]").sendVKey 12
    session.findById("wnd[0]/tbar[0]/okcd").Text = "/n"
    session.findById("wnd[0]").sendVKey 0
End Sub

As I have the impression that I can do nothing to close the file within the subroutines (as it only opens after the run time) I'm currently looking for a way to:

either tell SAP software/SAP GUI not to open the file at all,
or to prohibit it from being able to access Excel,
or maybe just close SAP GUI altogether and see if that works - although I would prefer not to do that.

I think there will be two sessions running simultaneously (export file opens in new one), look for the one with the given Workbook name. Close this workbook/session, then reopen it in the current session. — user3819867, Oct 21 '15 at 11:23
Thanks for the answer, but could you explain it to me? I could not close the workbook "export.xlsx" in any way, because it is not open during the runtime, but opens afterwards. Is closing a "session" different? — C. Dan, Oct 21 '15 at 12:34
Of course it takes up to a minute for SAP to export it, you could of course wait for it. Technically I'd try hitting `Back` until I succeed (blocked while the export is going, single thread?) then would `Wait` two more seconds for the workbook to open. — user3819867, Oct 22 '15 at 08:58
It's possible to work with any Windows application by using [`GetObject`](https://answers.sap.com/questions/737136/sap-gui-74-patch-12---disallow-excel-auto-open.html) (by using either `winmgmts:` or the Excel file name) or `AppActivate` with [WScript shell](https://stackoverflow.com/questions/59847954/how-can-i-write-sap-gui-script-for-a-sap-pop-up-window). — Sandra Rossi, Nov 14 '20 at 14:54

score 0 · Answer 1 · answered Nov 02 '15 at 09:42

As far as I know, SAP SDK export functions always use a dummy file in order to process exporting of data. This is by so, created via the Auto Open exported file calls. You may try to allow this in order for you to open new exported file after calling refresh() method. See http://rmps.cygnaltech.net/?p=779

score 0 · Answer 2 · edited Jun 21 '19 at 13:48

My VBA routines usually do a lot of work after I save the SAP output to Excel format. Like your experience, Excel immediately tries to open the file after the macro completes. I usually go right into post-processing the data in other VBA routines. I discovered that if I do two things in the VBA algorithm, the file isn't opened and the VBA isn't interrupted. Here's what I do:

Immediately after I export the file from SAP, I rename the file by concatenating the date and time before the .XLS in the filename. I save that name in the Excel file and open it separately. If your VBA ends after the export, you will get a file not found error in Excel.
After the rename, I immediately go on to open the renamed file or to other routines in the VBA, most of the time the open of the can't-be-found filename does not interrupt the VBA processing.

score 0 · Answer 3 · answered Mar 12 '21 at 14:56

I figured out a work around, which I observed one time when I ran a VBA with SAP scripting and Excel was too busy to answer SAP's open file request. So, when SAP goes to open the file, it tries to open it in the newest instance of Excel. If your script is running in a spreadsheet in an older instance, SAP will send the open file command to the new one, which means the Excel open error message will show up in the newer separate instance of Excel. Here's how you do it:

Open your spreadsheet with the macro
After it opens, hold down the ALT key
Still holding down ALT, new right mouse the Excel icon in the task bar
Still holding down ALT, select the Excel icon from the pop up menu
Still holding down ALT, eventually a message will appear to ask if you want a new instance, answer Yes
When the green Excel start up message is displayed, release the ALT key Make sure that the new instance is open to a blank spreadsheet
Now run your macro/script like normal.

Works like a charm in Excel 365. I have not tested it in older versions. Eventually, you have to go to the new instance of Excel and clear out all the error messages, one by one.

In addition to this, you may need to keep the new instance from opening the file anyway. I handle this by renaming the SAP export file in the directory immediately after exporting the file. I rename the file by adding a date stamp in the file name. That way I make sure that the new instance does not by chance open the exported file. So, if I'm always exporting to SAP_MMUsers.xlsx, I rename it to SAP_MMUsers_0312.xlsx in VBA coding. So, I'm always exporting to SAP_MMUsers.xlsx and only have to answer the SAP GUI "allow" questions the first time I run the macro/script. The export file name should not exist in the directory, so I don't have to code for the Replace option and I have the output saved with a timestamp if I need to go back and look at the original data. As a point of my process, I always delete any SAP export files in the directory that follow my naming convention for SAP exports (they always begin with "SAP_"). That way the process has some redundancy built in to the VBA code to avoid processing interruptions.

score 0 · Answer 4 · answered Dec 15 '21 at 03:43

I usually find it quicker to load the ALV table into an array and then export the array into the required location in Excel. This by-passes the need to open additional workbooks for processing. This approach is typically faster if there are < approx 1000 rows.

SAP GUI script opens an Excel window and I can't stop it

4 Answers4