Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Mar 2010
    Location
    Texas
    Posts
    54
    Thanks
    9
    Thanked 0 Times in 0 Posts
    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

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    What are you actually trying to do with the instances?
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    Star Lounger
    Join Date
    Mar 2010
    Location
    Texas
    Posts
    54
    Thanks
    9
    Thanked 0 Times in 0 Posts
    Trying to find all the open workbooks.

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    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.
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    Star Lounger
    Join Date
    Mar 2010
    Location
    Texas
    Posts
    54
    Thanks
    9
    Thanked 0 Times in 0 Posts
    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?

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    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
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    Star Lounger
    Join Date
    Mar 2010
    Location
    Texas
    Posts
    54
    Thanks
    9
    Thanked 0 Times in 0 Posts
    Excellent. Thanks. I wouldn't have come up with that.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •