Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

excel

vba

I want to use something similar to GetObject(,"Excel.Application") to get back the application I created.

I call CreateObject("Excel.Application") to create Excel instances. Later if the VBA project resets, due to debugging and coding, the Application object variables are lost but the Excel instances are running in the background. Kind of a memory leak situation.

I want to re-attach to either re-use (preferred way) or close them.

like image 216
NathaneilCapital Avatar asked May 21 '15 02:05

NathaneilCapital


People also ask

Why does Excel open multiple instances?

Beginning with Office 2013, each Excel workbook is displayed in a separate window by default, even though it is the same Excel instance. The reason is that Excel 2013 started to use the Single Document Interface (SDI), in which each document is opened in its own window and handled separately.

How do I find my instance of Excel?

To do this, press Ctrl + Shift + Esc . Then, click on the "Processes" tab. You should see a list of all the processes that are running on your computer. Look through the list and see if there are any other instances of "EXCEL.

What are multiple instances of Excel?

Yet within the same instance, you cannot use Excel while it's working. To prevent this, you can open two separate instances of Excel before running a resource consuming task. Having multiple instances open allows you to use one instance to run an intensive task, while you continue working in another.


1 Answers

To list the running instances of Excel:

#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 Test()
  Dim xl As Application
  For Each xl In GetExcelInstances()
    Debug.Print "Handle: " & xl.ActiveWorkbook.FullName
  Next
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
like image 168
Florent B. Avatar answered Sep 23 '22 08:09

Florent B.