VBA is sometimes not recognizing Excel file that has been opened through SAP GUI script

Question

I have a SAP GUI script running every day in VBA. In the script I am exporting some data from SAP to several different Excel files, and these are saved to a network drive. In the first macro, I export data. In the second I copy the data to the same workbook as the script is in.

Some days I get a runtime error

Subscript out of range

on Set ws2 = Workbooks("FEBA_EXPORT_" & today2 & ".XLSX").Worksheets("Sheet1").

It looks like the Excel file is not recognized as open. I manually close the file, and reopen it and then the script will run.

I tried to insert the below code in front of the Set ws2 line that is giving an error, and this code is always giving the massage that the file is open.

Dim Ret
Ret = IsWorkBookOpen(filepath & "FEBA_EXPORT_" & today2 & ".XLSX")
If Ret = True Then
    MsgBox "File is open"
Else
    MsgBox "File is Closed"
End If

This is the function:

Function IsWorkBookOpen(FileName As String)
    Dim ff As Long, ErrNo As Long

    On Error Resume Next
    ff = FreeFile()
    Open FileName For Input Lock Read As #ff
    Close ff
    ErrNo = Err
    On Error GoTo 0

    Select Case ErrNo
    Case 0:    IsWorkBookOpen = False
    Case 70:   IsWorkBookOpen = True
    Case Else: Error ErrNo
    End Select
End Function

This is the relevant part of the code:

Sub CopyExportedFEBA_ExtractFEBRE()

    Dim SapGuiAuto As Object
    Dim SAPApp As Object
    Dim SAPCon As Object
    Dim session As Object

    Set SapGuiAuto = GetObject("SAPGUI")
    Set SAPApp = SapGuiAuto.GetScriptingEngine
    Set SAPCon = SAPApp.Children(0)
    Set session = SAPCon.Children.ElementAt(0) ' <--- Assumes you are using the first session open. '
    
    Dim ws0, ws1, ws2, ws6, ws7 As Worksheet
    
    Set ws0 = Workbooks("FEB_BSPROC.xlsm").Worksheets("INPUT")
    Set ws1 = Workbooks("FEB_BSPROC.xlsm").Worksheets("FEB_BSPROC")
    Set ws6 = Workbooks("FEB_BSPROC.xlsm").Worksheets("FBL3N_1989")
    
    Dim today2, filepath As String
    today2 = ws0.Range("E2")
    filepath = ws0.Range("A7")
    
    ' Check if  if FEBA_EXPORT wb is open
    ' This is giving the message that the file is open
    
    Dim Ret
    Ret = IsWorkBookOpen(filepath & "FEBA_EXPORT_" & today2 & ".XLSX")
    If Ret = True Then
        MsgBox "File is open"
    Else
        MsgBox "File is Closed"
    End If
    
    ' This is giving runtime error 9 Subscript out of range
    ' If manually close the Excel and the  reopen, then it will always work after this
    Set ws2 = Workbooks("FEBA_EXPORT_" & today2 & ".XLSX").Worksheets("Sheet1")
    
    'This is never giving any errors
    Set ws7 = Workbooks("1989_" & today2 & ".XLSX").Worksheets("Sheet1")

The filepath varaiable is the full filepath to the network drive. So this is not the issue. Also I have another Excel file that is opened in the same way, and that one is never giving any errors.
The today2 variable is also correct.

I thought that it would work if I could close the ws2 workbook with VBA and then reopen it. So I tried to close it without setting it to a variable, but then I got the same error.

With SAP GUI scripting when you export anything to an Excel file, the file will open automatically after it has been saved. I am wondering if this could be the issue? I only have problems with this one Excel file, and not with any of several others that are saved and opened in the same way.

Answer 1

As I said in my above comment, the workbook may be open in a new session, different from the one where the code runs. Please, use the next function to identify if it is a matter of different Excel session:

Function sameExSession(wbFullName As String, Optional boolClose As Boolean) As Boolean
   Dim sessEx As Object, wb As Object
  
   Set sessEx = GetObject(wbFullName).Application
   If sessEx.hwnd = Application.hwnd Then
        sameExSession = True
   Else
        sameExSession = False
        If boolClose Then
            sessEx.Workbooks(Right(wbFullName, Len(wbFullName) - InStrRev(wbFullName, "\"))).Close False
            sessEx.Quit: Set sessEx = Nothing
        End If
   End If
End Function

It identify the session where the workbook is open, then compare its handle with the active session one and if not the same, close the workbook (if calling the function with second parameter as True), quit the session and returns False. If only checking, call the function with the second parameter being False (the workbook will not be closed, and session will still remain).

It can be used in the next way:

Sub testSameExSession()
   Dim wbFullName As String, wbSAP As Workbook
   wbFullName = filepath & "FEBA_EXPORT_" & today2 & ".XLSX"
   If sameExSession(wbFullName, True) Then
        Debug.Print "The same session"
        Set wbSAP = Workbooks("FEBA_EXPORT_" & today2 & ".XLSX")
   Else
        Debug.Print "Different session..."
        Set wbSAP = Workbooks.Open(wbFullName)        
   End If
   Debug.Print wbSAP.Name
   'use the set workbook to do what you need...
End Sub

When you have the described problem, please use the above way to test if it is a matter of different sessions.

If so, is easy to input this part in your existing code, I think. If the workbook will be open in a different session, no need to manually close it (and reopen), the above function does it...

Answer 2

In case someone is still facing this issue, I found a way to wait for the excel files downloaded from SAP and its app instance to open, then close them and let you work with the files without troubles. You can set a timeout too.

If files are downloaded and opened in an already open instance of excel, it will just close the files and not the whole instance.

You can use it as follow:

Sub Test()
    Call Close_SAP_Excel("Test.xlsx", "Test2.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(ParamArray FileNames())
    '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 FinishedLoop As Boolean, TimeoutReached As Boolean, FileClosed As Boolean
    Dim ReTry As Long
    Dim i As Long, x 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
                For x = LBound(FileNames()) To UBound(FileNames())
                    If xls.Name = FileNames(x) 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
                        FileClosed = True
                    
                    End If
                Next x
            Next
        Next
        
        If FileClosed Then
            FinishedLoop = True
        End If
        i = i + 1
    Loop
    
    ThisWorkbook.Activate

    If Not TimeoutReached Then
        If FileClosed Then
            On Error Resume Next
            If ExcelAppSAP.Workbooks.Count = 0 Then
                ExcelAppSAP.Quit
            End If
        Else
            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