asked on

Excel VBA - Get an Excel instance from the window handle

I have two Excel instances running. I am trying to get one instance to be able to access the second. I can find the window handle fine. How can I translate the handle into an Excel.Application object?

My current attempt is shown below:

Public Declare Function AccessibleObjectFromWindow Lib "oleacc" (ByVal hwnd As Long, ByVal dwId As Long, riid As tGUID, ppvObject As Object) As Long

Public Declare Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long

Public xl As Excel.Application

Dim lHwnd As Long
'Find Excel's window handle
lHwnd = FindWindow("XLMAIN", "Test")
If (lHwnd <> 0) Then
    Set xl = IAccessibleFromHwnd(lHwnd)
end if


'Retrieve the IAccessible interface from a window handle
'Reference:Jean Ross,Chapter 17: Accessibility in Visual Basic,Advanced Microsoft Visual Basic 6.0, 2nd Edition
Function IAccessibleFromHwnd(hwnd As Long) As IAccessible
    Dim oIA As IAccessible
    Dim tg As tGUID
    Dim lReturn As Long

    ' Define the GUID for the IAccessible object
    ' {618736E0-3C3D-11CF-810C-00AA00389B71}
    With tg
        .lData1 = &H618736E0
        .nData2 = &H3C3D
        .nData3 = &H11CF
        .abytData4(0) = &H81
        .abytData4(1) = &HC
        .abytData4(2) = &H0
        .abytData4(3) = &HAA
        .abytData4(4) = &H0
        .abytData4(5) = &H38
        .abytData4(6) = &H9B
        .abytData4(7) = &H71
    End With

    ' Retrieve the IAccessible object for the form
    lReturn = AccessibleObjectFromWindow(hwnd, 0, tg, oIA)
    Set IAccessibleFromHwnd = oIA
End Function

Open in new window

Rory Archibald

If you know the name of a workbook in the other instance, using GetObject is much simpler?
Regards,
Rory

jnash67

ASKER

I'm having some difficulties getting GetObject to work. FindWindow finds the handle confirming the it is there. For filename I have tried the fully qualified path, just the filename and "".

When I tried it with "", it returned the Excel instance I was in, not the other one.

Public xl As Excel.Application

Dim lHwnd As Long
'Find Excel's window handle
lHwnd = FindWindow("XLMAIN", "Test")
If (lHwnd <> 0) Then
    On Error Resume Next
    Set xl =  = GetObject(filename, "Excel.Application")
    On Error Goto 0
end if

Open in new window

SOLUTION

Rory Archibald

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

nffvrxqgrcfqvvc

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jnash67

ASKER

Got it working as attached below.

Rory - Your approach worked as well:

Set dashboardWorkbook = GetObject(dashboardFilename, "Excel.Sheet")

except I got a popup to update links that I couldn't figure out how to programmatically surpress. Any ideas (besides not having external links in the workbook)?

Dim lHwnd As Long, lChld As Long
        lHwnd = FindWindow("XLMAIN", dashboardCaption)
        If (lHwnd <> 0) Then
            lChld = FindChildWindow(lHwnd, "EXCEL7")
            Set xl = GetExcelApplicationFromWorkbookHwnd(lChld)
        End if

Public Function GetExcelApplicationFromWorkbookHwnd(ByVal hWnd As Long) As Excel.Application

    Dim iid As tGUID
    Dim hr As Long
    Dim obj As Object
    Dim fOk As Boolean

    fOk = False

    Call IIDFromString(StrPtr(IID_IDispatch), iid)
    If AccessibleObjectFromWindow(hWnd, OBJID_NATIVEOM, iid, obj) = 0 Then    'S_OK
        Set GetExcelApplicationFromWorkbookHwnd = obj.Application
    Else
        Set GetExcelApplicationFromWorkbookHwnd = Nothing
    End If

End Function

Open in new window

Rory Archibald

If the workbook is already open, I can't imagine why you would get an update links dialog.

jnash67

ASKER

Thanks for the help. I'll have to investigate the update links issue at a later date.