List all open workbooks in all instances of Excel.

cytop · Sep 23rd 2013

As the title says - will find all instances of Excel and list (to the debug window) all workbooks open. Can be very easily extended for other functionality

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 IIDFromString Lib "ole32" _
                        (ByVal lpsz As Long, ByRef lpiid As GUID) As Long
                        
Private Declare Function AccessibleObjectFromWindow Lib "oleacc" _
                        (ByVal hWnd As Long, ByVal dwId As Long, ByRef riid As GUID, _
                         ByRef ppvObject As Object) As Long


Private Type GUID
    Data1 As Long
    Data2 As Integer
    Data3 As Integer
    Data4(7) As Byte
End Type


Private Const RETURN_OK As Long = &H0
Private Const IID_IDispatch As String = "{00020400-0000-0000-C000-000000000046}"
Private Const OBJID_NATIVEOM As Long = &HFFFFFFF0


Sub ComplexTest()


    Dim iCounter As Long
    Dim hWndXL As Long
    Dim oXLApp As Object
    Dim oWB As Object
    Dim oWS As Object


    '// First the first Excel Window
    hWndXL = FindWindowEx(0&, 0&, "XLMAIN", vbNullString)
    
    '// Got one, at least...?
    Do While hWndXL > 0
        
        '// Increment counter
        iCounter = iCounter + 1
        
        '// Print Instance & Handle to Debug window
        Debug.Print "Instance #" & iCounter & ": "; "Handle: " & hWndXL
        
        '// Get a reference to it
        If GetReferenceToXLApp(hWndXL, oXLApp) Then
        
            '// Iterate through the workbook
            For Each oWB In oXLApp.Workbooks
                '// Print Workbook name to DEBUG window
                Debug.Print , oWB.Name
                
                '// It would be easy enough to check for a workbook name
                '// and close it here - the name would be passed as a
                '// parameter and the procedure changed to a function
                '// to return Success, or otherwise, to the calling procedure
                '// With oWB
                '//    .Saved = True
                '//    .Close
                '// End With
                
                '// List worksheets
                For Each oWS In oWB.Worksheets
                    Debug.Print , , oWS.Name
                Next
            Next
        End If
        
        '// Find the next Excel Window
        hWndXL = FindWindowEx(0, hWndXL, "XLMAIN", vbNullString)
        
    Loop


End Sub


'// Returns a reference to a specific instance of Excel.
'// The Instance is defined by the Handle (hWndXL) passed
'// by the calling procedure
Function GetReferenceToXLApp(hWndXL As Long, oXLApp As Object) As Boolean
    
    Dim hWinDesk As Long
    Dim hWin7 As Long
    
    Dim obj As Object
    Dim iID As GUID


    '// Rather than explaining, go read
    '// http://msdn.microsoft.com/en-us/library/windows/desktop/ms687262(v=vs.85).aspx
    Call IIDFromString(StrPtr(IID_IDispatch), iID)
    
    '// We have the XL App (Class name XLMAIN)
    '// This window has a child called 'XLDESK' (which I presume to mean 'XL desktop')
    '// XLDesk is the container for all XL child windows....
    hWinDesk = FindWindowEx(hWndXL, 0&, "XLDESK", vbNullString)
    
    '// EXCEL7 is the class name for a Workbook window (and probably others, as well)
    '// This is used to check there is actually a workbook open in this instance.
    hWin7 = FindWindowEx(hWinDesk, 0&, "EXCEL7", vbNullString)
    
    '// Deep API... read up on it if interested.
    '// http://msdn.microsoft.com/en-us/library/windows/desktop/dd317978(v=vs.85).aspx
    If AccessibleObjectFromWindow(hWin7, OBJID_NATIVEOM, iID, obj) = RETURN_OK Then
        Set oXLApp = obj.Application
        GetReferenceToXLApp = True
    End If
    
End Function

Display More

**pike** · Sep 23rd 2013

Re: List all open workbooks in all instances of Excel.

this will be very handy
thanks cytop

cytop · Jan 7th 2014

Re: List all open workbooks in all instances of Excel.

Revised to actually return a reference to an Excel Application, workbook or worksheet based on the App title, workbook name or worksheet tab name.

See Sub Test for an example.

Code

Option Explicit
Option Compare Text


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 _
    IIDFromString Lib "ole32" _
                    (ByVal lpsz As Long, ByRef lpiid As GUID) As Long


Private Declare Function _
    AccessibleObjectFromWindow Lib "oleacc" _
                    (ByVal hWnd As Long, ByVal dwId As Long, ByRef riid As GUID, _
                     ByRef ppvObject As Object) As Long


Private Type GUID
    Data1 As Long
    Data2 As Integer
    Data3 As Integer
    Data4(7) As Byte
End Type


Private Const RETURN_OK As Long = &H0
Private Const IID_IDispatch As String = "{00020400-0000-0000-C000-000000000046}"
Private Const OBJID_NATIVEOM As Long = &HFFFFFFF0


Public Enum ExternalRef
    Worksheet
    Workbook
    Application
End Enum


Sub Test()


    Dim x As Excel.Application
    Dim y As Excel.Workbook
    Dim z As Excel.Worksheet


    '// Get a reference to an instance of Excel by finding a partial title
    '// As finding a reference to an external Excel App is the default, only
    '// the first 2 parameters need be passed
    If GetExternalRef("Book1", x) Then
        Debug.Print x.Caption
    End If


    '// Get a reference to a workbook object by finding a partial workbook name
    If GetExternalRef("Book1", y, Workbook) Then
        Debug.Print y.Name
    End If


    '// Get a reference to a worksheet object from finding a partial sheet name
    If GetExternalRef("Sheet3", z, Worksheet) Then
        Debug.Print z.Name
    End If
    
    '// Obviously, as these are searches on a partial name/title - the passed name should be
    '// as complete as possible to ensure the correct instance/workbook/worksheet is found.
    '// Especially important if searching for a sheet with the default name...!
    
End Sub


Public Function GetExternalRef(ByVal strName As String, _
                                   ByRef oObject As Variant, _
                                   Optional oWhich As ExternalRef = ExternalRef.Application) _
                                   As Boolean
                                   
    '// Parameters:
    '// strName - The text to search for. Just a normal string parameter
    '// oObject - An Object. This can be a reference to an Excel Application, Workbook
    '//           or Worksheet. Defined by the calling procedure. This is explicitly
    '//           declared as ByRef to make it clear its value may be changed by the
    '//           procedure. However, as variables are passed byRef by default in VBA
    '//           this is more a convention than anything else
    '// oWhich -  Which object to return - the application, workbook or worksheet. This
    '//           is defined as an ENum to assist the user when coding. The possible options
    '//           will be displayed when typing. Also, is an optional parameter which means
    '//           if no value is passed it will default to Application.
    
    '// Dim iCounter As Long
    Dim hWndXL As Long
    Dim oXLApp As Object
    Dim oWB As Object
    Dim oWS As Object


    '// Find the first Excel Window
    hWndXL = FindWindowEx(0&, 0&, "XLMAIN", vbNullString)


    '// Do while any found...
    Do While hWndXL > 0


        '// Increment counter
        '// iCounter = iCounter + 1


        '// Print Instance & Handle to Debug window
        '// Debug.Print "Instance #" & iCounter & ": "; "Handle: " & hWndXL


        '// Get a reference to it
        If GetReferenceToXLApp(hWndXL, oXLApp) Then


            '// All string comparisons RELY on the 'Option Compare Text' in the General declarations
            '// as Excel is case sensitive by default
            If oWhich = Application And InStr(oXLApp.Caption, strName) > 0 Then
                
                Set oObject = oXLApp
                GetExternalRef = True
                
                Exit Function
                
            End If
            
            '// Iterate through the workbooks
            For Each oWB In oXLApp.Workbooks
                '// Print Workbook name to DEBUG window
                '// Debug.Print , oWB.Name


                If oWhich = Workbook And InStr(oWB.Name, strName) > 0 Then


                    Set oObject = oWB


                    GetExternalRef = True
                    Exit Function


                End If


                '// Loop through worksheets
                For Each oWS In oWB.Worksheets
                    
                    '// Debug.Print , , oWS.Name


                    If oWhich = Worksheet And InStr(oWS.Name, strName) > 0 Then


                        Set oObject = oWS


                        GetExternalRef = True
                        Exit Function


                    End If


                Next
            Next
        End If


        '// Find the next Excel Window
        hWndXL = FindWindowEx(0, hWndXL, "XLMAIN", vbNullString)


    Loop


End Function


'// Returns a reference to a specific instance of Excel.
'// The Instance is defined by the Handle (hWndXL) passed
'// by the calling procedure
Function GetReferenceToXLApp(hWndXL As Long, oXLApp As Object) As Boolean


    Dim hWinDesk As Long
    Dim hWin7 As Long


    Dim obj As Object
    Dim iID As GUID


    '// Rather than explaining, go read
    '// http://msdn.microsoft.com/en-us/library/windows/desktop/ms687262(v=vs.85).aspx
    Call IIDFromString(StrPtr(IID_IDispatch), iID)


    '// We have the XL App (Class name XLMAIN)
    '// This window has a child called 'XLDESK' (which I presume to mean 'XL desktop')
    '// XLDesk is the container for all XL child windows....
    hWinDesk = FindWindowEx(hWndXL, 0&, "XLDESK", vbNullString)


    '// EXCEL7 is the class name for a Workbook window (and probably others, as well)
    '// This is used to check there is actually a workbook open in this instance.
    hWin7 = FindWindowEx(hWinDesk, 0&, "EXCEL7", vbNullString)


    '// Deep API... read up on it if interested.
    '// http://msdn.microsoft.com/en-us/library/windows/desktop/dd317978(v=vs.85).aspx
    If AccessibleObjectFromWindow(hWin7, OBJID_NATIVEOM, iID, obj) = RETURN_OK Then
        Set oXLApp = obj.Application
        GetReferenceToXLApp = True
    End If


End Function

Display More

snb · Apr 14th 2014

Re: List all open workbooks in all instances of Excel.

or ?

Code

Sub M_snb()
    With CreateObject("Word.Application")
        If .Tasks.exists("Microsoft Excel") Then
           For Each ts In .Tasks
              If InStr(ts.Name, "Microsoft Excel") = 1 Or InStr(ts.Name, ".xls") Then c01 = c01 & vbLf & ts.Name
           Next
        End If
        .Quit
    End With
    
    MsgBox c01
End Sub

Display More

**pike** · Apr 14th 2014

Re: List all open workbooks in all instances of Excel.

thats cool!

**Ger Plante** · Apr 15th 2014

Re: List all open workbooks in all instances of Excel.

WHAT!!!

I can't believe my eyes...

I even walked away, got a cup of tea and looked again...

But its still there...

a "For" loop .... from SNB...

I need to go and lie down...

snb · Apr 15th 2014

Re: List all open workbooks in all instances of Excel.

ger

If I were you I wouldn't: it's agains this forum's etiquette !

I'd prefer this approach:

http://www.ozgrid.com/forum/sh…87605&p=709806#post709806

**Ger Plante** · Apr 15th 2014

Re: List all open workbooks in all instances of Excel.

:spin:

List all open workbooks in all instances of Excel.

Participate now!

Share