SAP GUI Export to Excel Auto-Open Problem with Excel 365

[jmessingschlager]

Hi

This problem has been batted around for a while but with Excel 365 it has gotten pretty bad. My company just upgraded us from Excel 2013 to 365. In the old version, the SAP export always opened in the same Excel instance so it was easy to manage in the VBA. In Excel 365, I have not been able to find rhyme or reason to how the file is opened. sometimes in the same Excel instance while other times in a new instance.

We need an elegant solution for this problem, which would be a setting in the SAP GUI to turn off this function. As with may developers I build reports that combine scripting with VBA to generate SAP output, download it, and process it. Many of these processes run hands-off in the early morning. Now with Excel 365, I have to be present when the process is running to close the instance to allow the Excel VBA process to continue running. The reason I exist is so we don't need to do this.

The proposed solutions to basically abort other instance Excel processes does not always work if the SAP report is very large and the Excel process gets ahead of the SAP auto-open process and closes the last instance before the new instance opens, which often times is the very process running the report.

If there is a way to turn off this function, please share - we're using SAP NetWeaver GUI Version 7500.2.5.1147 Patch Level 5.

Joe

[Sandra_Rossi]

I'm pretty sure SAP just opens the file generically, they don't mind how technically it opens. If you want SAP to change something contact the SAP support.

Otherwise, you'd better ask the question in an Excel/Windows forum.

NB: I think that VBA may find the Excel instance by searching by the opened file name (GetObject("C:\filename.xlsx"))

[jmessingschlager · Accepted solution]

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:

1. Open your spreadsheet with the macro
2. After it opens, hold down the ALT key
3. Still holding down ALT, new right mouse the Excel icon in the task bar
4. Still holding down ALT, select the Excel icon from the pop up menu
5. Still holding down ALT, eventually a message will appear to ask if you want a new instance, answer Yes
6. When the green Excel start up message is displayed, release the ALT key
7. Make sure that the new instance is open to a blank spreadsheet
8. 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.

[alexrivax]

Hello guys,

In case someone is still facing this issue, I found a way to close the excel files downloaded from SAP and its app instance. It will wait until file is open and you can set the max timeout too.

You can place this in a separated Module and then call it from anywhere as follows:

Sub Test()
    Call Close_SAP_Excel("Test.xlsx")
End Sub

xCloseExcelFromSAP

#If VBA7 Then
  Private Declare PtrSafe Function AccessibleObjectFromWindow Lib "oleacc" ( _
    ByVal hwnd As LongPtr, ByVal dwId As Long, riid As Any, ppvObject As Object) As Long
  Private Declare PtrSafe Function FindWindowExA Lib "user32" ( _
    ByVal hwndParent As LongPtr, ByVal hwndChildAfter As LongPtr, _
    ByVal lpszClass As String, ByVal lpszWindow As String) As LongPtr
#Else
  Private Declare Function AccessibleObjectFromWindow Lib "oleacc" ( _
    ByVal hwnd As Long, ByVal dwId As Long, riid As Any, ppvObject As Object) As Long
  Private Declare Function FindWindowExA Lib "user32" ( _
    ByVal hwndParent As Long, ByVal hwndChildAfter As Long, _
    ByVal lpszClass As String, ByVal lpszWindow As String) As Long
#End If

Sub Close_SAP_Excel(FileName As String)
    'Procedure to close files downloaded from SAP and at the same time close the Excel application instance that will be open with them.
    Dim ExcelAppSAP As Variant
    Dim ExcelFile As Variant
    Dim FileName As String
    Dim FinishedLoop As Boolean, TimeoutReached As Boolean
    Dim ReTry As Long
    Dim i As Long
    
    Set ExcelAppSAP = Nothing
    ReTry = 100000 'Used as Timeout 100000 = ~10 seconds
    i = 1
    
    'The following loop is executed until excel file is closed.
    'Inside of this, there is a For Loop for each Excel Instance and inside of that is another loop
    'for each excel inside the instance. If name matches, it is closed.
    Do While Not FinishedLoop
        If i > ReTry Then
            TimeoutReached = True
            Exit Do
        End If
        
        For Each ExcelFile In GetExcelInstances() 'Function to Get Excel Open Instances
            For Each xls In ExcelFile.Workbooks
                If xls.Name = FileName Then
                
                    Set ExcelAppSAP = ExcelFile 'Set Instance opened by SAP to variable
                    'Here add actions if needed. Reference to workbook as xls e.g.: xls.Sheets(1).Range("A1").Copy
                    xls.Close SaveChanges:=False
                    File1Downloaded = True
                
                'In case of more than one file, and ElseIf can be added with a new variable to close it too.
                End If
            Next
        Next
        
        If File1Downloaded Then
            FinishedLoop = True
        End If
        i = i + 1
    Loop
    
    If Not TimeoutReached Then
        If File1Downloaded Then
            On Error Resume Next
            ExcelAppSAP.Quit
        Else
            ThisWorkbook.Activate
            MsgBox "Excel application instance from SAP was not closed correctly. Please close it manually or try again.", , "Error"
        End If
    Else
        MsgBox "Max timeout reached", , "Error"
    End If
End Sub
     
Public Function GetExcelInstances() As Collection
  Dim guid&(0 To 3), acc As Object, hwnd, hwnd2, hwnd3
  guid(0) = &H20400
  guid(1) = &H0
  guid(2) = &HC0
  guid(3) = &H46000000
  Set GetExcelInstances = New Collection
  Do
    hwnd = FindWindowExA(0, hwnd, "XLMAIN", vbNullString)
    If hwnd = 0 Then Exit Do
    hwnd2 = FindWindowExA(hwnd, 0, "XLDESK", vbNullString)
    hwnd3 = FindWindowExA(hwnd2, 0, "EXCEL7", vbNullString)
    If AccessibleObjectFromWindow(hwnd3, &HFFFFFFF0, guid(0), acc) = 0 Then
      GetExcelInstances.Add acc.Application
    End If
  Loop
End Function

[former_member1074678]

I actually solved this in Excel 365 by adding code in VBA, after step where program starts to generate / replace report. Code - Wait function:

 Sub Wait(seconds As Integer)
      Dim now As Long
      now = Timer()
      Do
          DoEvents
      Loop While (Timer < now + seconds)
    End Sub
Call Wait(5)

After this Excel launches export.xlsx file in the same instance.

[former_member1143260]

Hi Script Man,

in the linked thread you've posted:

The simplest method would be to disable the security settings: (Alt /F12 => Options => Security => Security Options => Status => deactivated) If you need special settings, you would have to ask someone from IT in your company. – ScriptManSep 25 '20 at 10:01

Does this mean that, if I set Options => Security => Security Options => Security Module =>Status => "disabled" (this looks similar to what you posted...) there will be no automatic open process of downloaded excel files?

I'm a little bit afraid of changing security options...

Thanks for your help,

Lutz

[script_man]

Hi Lutz,

Please see below:

https://stackoverflow.com/questions/64009373/close-sap-export-multiple-excel-file-with-vba

My answer from 23.09.2020 can be found there.

Regards, ScriptMan

[former_member1143260]

Hi Joe,

did you find a solution for your problem?

It's exactly the problem I'm facing, but obviously nobody else has this problem...

I have a code, which exports, opens, processes and closes an Excel-file, that runs perfect as long as the exported file is small. But if the file is to big, then the excel-getobject is faster then the SAP auto-open and I'm facing an error (workbook is already opened by another user),which I cannot handle by my code because it's not caused by the code.

Best regards,

Lutz

[script_man]

Hi Lutz,

It has been a long time, but this problem also bothered me. Please check whether you can get a suggestion for solving your problem from the link below:

https://stackoverflow.com/questions/64009373/close-sap-export-multiple-excel-file-with-vba

Regards, ScriptMan