Finding All Excel Instances

Topic

New Reply

How can you get to all the running Excel instances?

I use to the following code for other reasons, so could it be applied here? If not, what are some other options?

sub ListRunningProcesses()
Dim winmgmtsObject As Object
Set winmgmtsObject = GetObject(“winmgmts:”)

Dim myQuery As String
myQuery = “SELECT * FROM win32_process WHERE Name = ‘EXCEL.EXE'”

Dim allProcesses As Object
Set allProcesses = winmgmtsObject.execquery(myQuery)

Dim myExcelInstance As Excel.Application
Dim excelApplication As Excel.Application
Dim myProcess As Object
For Each myProcess In allProcesses
Debug.Print myProcess.Name
‘set myExcelInstance = ???
‘Do want I need to with a valid myExcelInstance reference
Next

‘object clean up goes here
End Sub

Reply | Quote

Replies

What are you actually trying to do with the instances?

Reply | Quote

Trying to find all the open workbooks.

Reply | Quote

To do what – list them, or actually manipulate them?
The latter is quite tricky since only the first instance of Excel actually registers itself in the ROT, so GetObject needs a specific workbook in order to grab an application instance other than the first loaded one.

Reply | Quote

Just listing them is good enough. From there I can get the owing instance. How would list all the open workbooks from among the Excel instances?

Reply | Quote

You can use API calls – note it lists all workbooks, including hidden and add-ins:

Code:

Option Explicit

Private Declare Function FindWindowEx Lib "User32" Alias "FindWindowExA" _
(ByVal hWnd1 As Long, ByVal hWnd2 As Long, ByVal lpsz1 As String, ByVal lpsz2 As String) As Long
Private Declare Function GetClassName Lib "User32" Alias "GetClassNameA" _
(ByVal hwnd As Long, ByVal lpClassName As String, ByVal nMaxCount As Long) As Long
Private Declare Function GetWindowText Lib "User32" Alias "GetWindowTextA" _
(ByVal hwnd As Long, ByVal lpString As String, ByVal cch As Long) As Long
Sub GetAllWorkbookWindowNames()
    Dim hWndMain As Long
    Dim colWins As Collection
    Dim n As Long
    hWndMain = FindWindowEx(0&, 0&, "XLMAIN", vbNullString)
    
    Set colWins = New Collection
    
    Do While hWndMain  0
        GetWbkWindows hWndMain, colWins
        hWndMain = FindWindowEx(0&, hWndMain, "XLMAIN", vbNullString)
    Loop
    
    For n = 1 To colWins.Count
        Debug.Print colWins(n)
    Next n
End Sub
Private Sub GetWbkWindows(ByVal hWndMain As Long, ByRef colWindows As Collection)
   Dim hwnd As Long, lngRet As Long, y As Integer, hWndDesk As Long
   Dim strText As String, strWin As String
   hWndDesk = FindWindowEx(hWndMain, 0&, "XLDESK", vbNullString)
   If hWndDesk  0 Then
        hwnd = FindWindowEx(hWndDesk, 0, vbNullString, vbNullString)
        Do While hwnd  0
           strText = String$(100, Chr$(0))
           lngRet = GetClassName(hwnd, strText, 100)
           If Left$(strText, lngRet) = "EXCEL7" Then
              strText = String$(100, Chr$(0))
              lngRet = GetWindowText(hwnd, strText, 100)
              ' add to collection
              If lngRet > 0 Then colWindows.Add Left$(strText, lngRet), CStr(hwnd)
           End If
           hwnd = FindWindowEx(hWndDesk, hwnd, vbNullString, vbNullString)
        Loop
        On Error Resume Next
    End If
End Sub

Reply | Quote

Excellent. Thanks. I wouldn’t have come up with that.

Reply | Quote