How can I use VBA to close Workbooks opened in a separate instance of Excel?

Question

I have an Excel file that runs various queries in SAP, which are then exported to Excel and then opened in a new Excel instance. I don't want those files to be open. Unfortunately, there doesn't seem to be a great way to tell SAP to stop doing that. What I thought would be an easy workaround would be to just close the ~15 files it opens by adding a loop to my original script; however, the macro seems to have limited scope and can only close files contained in 'this' instance of Excel.

I've read through Stackoverflow and many older forums and no one really seems to have a way to do this. This code works fine if the files are contained in the same instance as the macro but doesn't seem to reach outside.

For Each PartNumber In PNArray

    exportFileName = PartNumber & "_Export.xlsx"
    Workbooks(exportFileName).Close SaveChanges:=False

Next PartNumber

Does anyone have any neat tricks for how I can close these files? I'd prefer a targeted (by file name) approach, but might be able to just kill the entire instance instead.

Answer 1

I have this bit of code which returns a collection of all running Excel instances. You can loop over the workbooks in each returned instance and decide which ones need to be closed and/or which instances to Quit.

Source: Having multiple Excel instances launched, how can I get the Application Object for all of them?

'(32-bit)
Declare Function AccessibleObjectFromWindow Lib "oleacc" ( _
    ByVal hwnd As Long, ByVal dwId As Long, riid As Any, ppvObject As Object) As Long

Declare Function FindWindowExA Lib "user32" ( _
    ByVal hwndParent As Long, ByVal hwndChildAfter As Long, _
    ByVal lpszClass As String, ByVal lpszWindow As String) As Long


Sub Tester()
    Dim col As Collection, xl As Object, wb As Object
    
    Set col = GetExcelInstances()
    Debug.Print col.count & " instance(s) of Excel found"
    
    For Each xl In col
        Debug.Print "----------------------"
        Debug.Print "Instance " & xl.hwnd & " workbooks:"
        For Each wb In xl.Workbooks
            Debug.Print , wb.Name
        Next wb
    Next xl
End Sub

'return a collection of all open Excel instances
Private 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
    Dim AlreadyThere As Boolean
    Dim xl As Application
    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
            AlreadyThere = False
            For Each xl In GetExcelInstances
                If xl Is acc.Application Then
                    AlreadyThere = True
                    Exit For
                End If
            Next
            If Not AlreadyThere Then
                GetExcelInstances.Add acc.Application
            End If
        End If
    Loop
End Function

Answer 2

With a simple code, you can close all instances of Excel. Please note that the active workbook, which contains the following code, will also be closed.

Sub CloseAllExcelProcesses()
    Dim objExcel As Object
    For Each objExcel In GetObject("winmgmts:").ExecQuery("Select * from Win32_Process Where Name = 'EXCEL.EXE'")
        objExcel.Terminate
    Next objExcel
End Sub