Detect Other Instance of Open Workbooks

Question

I'm trying to have the user select an instance or open Workbook of Excel. The idea is to have a window that will display all open Instances of Excel and then display the Workbooks within these instances. I've done some self research and what I've found below...

Public Declare Function GetDesktopWindow Lib "user32" () As Long
Public 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

Function ExcelInstances() As Long
    Dim hWndDesk As Long
    Dim hWndXL As Long

    'Get a handle to the desktop
    hWndDesk = GetDesktopWindow

    Do
        'Get the next Excel window
        hWndXL = FindWindowEx(GetDesktopWindow, hWndXL, _
          "XLMAIN", vbNullString)

        'If we got one, increment the count
        If hWndXL > 0 Then
            ExcelInstances = ExcelInstances + 1
        End If

        'Loop until we've found them all
    Loop Until hWndXL = 0
End Function

Problem:

When I ran the code, I am getting the error message:

Compile Error:

Only comments may appear after End Sub, End Function or End Property

It's highlighting the first line in the code, and I believe it has something to do with the "user32" string?

Question: This code will only give me a COUNT of how many instances of Excel are currently open. Is there any way to return the names of the instances and then another sub routine that would return the Workbooks within the instances as well? I've seen a solution making use of VB.Net; however I'd like to avoid this so that I can try to keep everything consolidated into a single Excel Spreadsheet (if possible).

Answer 1

The following code sholuld answer your second question

Option Explicit

Private Declare Function GetClassName Lib "user32.dll" Alias "GetClassNameA" ( _
                                      ByVal hwnd As Long, _
                                      ByVal lpClassName As String, _
                                      ByVal nMaxCount As Long) As Long
Private Declare Function EnumWindows Lib "user32.dll" ( _
                                     ByVal lpEnumFunc As Long, _
                                     ByVal lParam As Long) As Boolean
Private Declare Function EnumChildWindows Lib "user32.dll" ( _
                                          ByVal hWndParent As Long, _
                                          ByVal lpEnumFunc As Long, _
                                          ByVal lParam As Long) As Long
Private Declare Sub IIDFromString Lib "ole32.dll" ( _
                                  ByVal lpsz As String, _
                                  ByRef lpiid As GUID)
Private Declare Sub AccessibleObjectFromWindow Lib "oleacc.dll" ( _
                                               ByVal hwnd As Long, _
                                               ByVal dwId As Long, _
                                               ByRef riid As GUID, _
                                               ByRef ppvObject As Any)

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

Private Const GC_CLASSNAMEEXCEL = "XLMAIN"
Private Const GC_CLASSNAMEEXCEL7 = "EXCEL7"
Private Const IID_EXCELWINDOW = "{00020893-0000-0000-C000-000000000046}"
Private Const OBJID_NATIVEOM = &HFFFFFFF0

Private lalngChildHwnd() As Long, lialngChildCount As Long
Private lalngMainHwnd() As Long, lialngMainCount As Long

Private Function GetApplications() As Application()

Dim ialngIndex As Long, ialngCount As Long
Dim udtGuid As GUID
Dim objWindow As Window
Dim aobjTempApplications() As Application

    'Alle lokalen Variablen zuruecksetzen
    Erase lalngChildHwnd
    lialngChildCount = 0
    Erase lalngMainHwnd
    lialngMainCount = 0

    'Konvertiere die IID des Excel-Window-Objektes in die GUID-Struktur
    Call IIDFromString(StrConv(IID_EXCELWINDOW, vbUnicode), udtGuid)

    'Callback Aufruf um alle Fenster zu klassifizieren
    Call EnumWindows(AddressOf EnumWindowsProc, ByVal 0&)

    'Schleife ueber alle gefundenen Parent-Excelfenster
    For ialngIndex = LBound(lalngMainHwnd) To UBound(lalngMainHwnd)

        'Callback Aufruf um alle Child-Fenster der
        'entsprechenden Parent-Fenster zu durchlaufen
        Call EnumChildWindows(lalngMainHwnd(ialngIndex), _
                              AddressOf EnumChildWindowsProc, ByVal 0&)

    Next

    'Schleife ueber die jeweils ersten gefundenen Window-Fenster
    For ialngIndex = LBound(lalngChildHwnd) To UBound(lalngChildHwnd)

        'Hole ueber die Zugriffsnummer das entsprechende Window-Objekt
        Call AccessibleObjectFromWindow(lalngChildHwnd(ialngIndex), _
                                        OBJID_NATIVEOM, udtGuid, objWindow)

        'Wenn das Objekt gefunden wurde setze einen Verweis
        'auf dessen Application-Objekt in das Array
        If Not objWindow Is Nothing Then

            ReDim Preserve aobjTempApplications(ialngCount)
            Set aobjTempApplications(ialngCount) = objWindow.Application
            ialngCount = ialngCount + 1

        End If
    Next

    'Array an die Funktionsvariable uebergeben
    GetApplications = aobjTempApplications

End Function

Private Function EnumWindowsProc( _
        ByVal pvlngHwnd As Long, _
        ByVal pvlnglParam As Long) As Long

'Callback Funktion um alle Fenster zu durchlaufen

'Wenn ein Excelfenster gefunden wurde schreibe dessen Handle in das Array
    If ClassName(pvlngHwnd) = GC_CLASSNAMEEXCEL Then

        ReDim Preserve lalngMainHwnd(lialngMainCount)
        lalngMainHwnd(lialngMainCount) = pvlngHwnd
        lialngMainCount = lialngMainCount + 1

    End If

    EnumWindowsProc = 1

End Function

Private Function EnumChildWindowsProc( _
        ByVal pvlngHwnd As Long, _
        ByVal pvlnglParam As Long) As Long

'Callback Funktion um alle Child-Fenster zu durchlaufen

'Wenn ein Window-Fenster im Excel-Fenster gefunden wurde schreibe
'dessen Handle in das Array und verlasse die Callback-Prozedur
    If ClassName(pvlngHwnd) = GC_CLASSNAMEEXCEL7 Then

        ReDim Preserve lalngChildHwnd(lialngChildCount)
        lalngChildHwnd(lialngChildCount) = pvlngHwnd
        lialngChildCount = lialngChildCount + 1

        EnumChildWindowsProc = 0

    Else

        EnumChildWindowsProc = 1

    End If
End Function

Private Function ClassName( _
        ByVal pvlngHwnd As Long) As String

'Funktion zum Ermitteln des Klassennames

Dim strClassName As String * 256
Dim lngReturn As Long

    'Lese den Klassenname des Handles
    lngReturn = GetClassName(pvlngHwnd, strClassName, Len(strClassName))

    'Klassenname an die Funktionsvariable uebergeben
    ClassName = Left$(strClassName, lngReturn)

End Function

The function GetApplications will give you all instances of Excel.