How to get Instance from Window Handle?

[PeSi]

Hi there

What I'm trying to do is 1) to check if a certain workbook is open in any running xlInstance (I've resolved that), 2) and then hook to that perticular xlInstance and open another workbook in it, and 3) finally make that xlInstance active (I've resolved that part too).

But how do I get an object pointer to that perticular xlInstance?

I know how to collect Window Handles to all running Excel Instances. But what I don't know is how to get the Application object from these Window Handles.

GetObject won't do because I've got zero control of what Excel instance is returned.

I've found some information about a Windows API Function called AccessibleObjectFromWindow. I've tried to use it but I'm failing to get the arguments right. The problem seems to be the 3rd argument. I just don't get it. Examples I've found seem to use it "differently" from case to case, and I can't find any details (or should I say, any details I understand) to resolve the problem.

Please, can anyone help med with this? Or is there a better way to get the Excel Instance from the Window Handle?

Your truly. PeSi

 

[BrianB]

As you know the handles, I think all you need is the API Function ....

Private Declare Function BringWindowToTop Lib "user32.dll" _
    (ByVal hWnd As Long) As Long

 

[PeSi]

Hello BrianB

Sorry, if I wasn't clear enough. I know how to move any running Excel instance to the top of Z-order. What I don't know is how to get an object reference to the Excel.Application Object of another running Excel instance.

Mayby it's better to explain what I'm trying to accomplish.
1. Check if a certain type of workbook is open in any running xlInstance.

Now, I can't do that by checking their names (I know how to do that). The name of the workbook is chosen by the user. I've put a hidden boolean value in the template workbook, and need to search for that perticular value. There is several ways to identify a workbook type by VB/VBA, but all ways I know of require access to the workbook object itself (or the Application or a Sheet object, ect. All "lower level objects" have the .Parent property to walk up the object tree.)

2. If the macro finds any running xlInstance with that perticular workbook type open, then I want to hook to that xlInstance and open another workbook in it (or in certain cases just activate that xlInstance). If the macro doesn't found any running xlInstance that fulfil the requirements, it creates a new xlInstance and open the workbook in it instead.

• I forget to mension, that these tests are run via a FrontLoader.EXE (made in VB6) that is launched by the user by double-clicking its icon on the Desktop.

So, somehow I need a reference to the relevant Workbook Object. I've been looking at an API function called AccessibleObjectFromWindow. Microsoft has made a VB6 example on how to use it with the very VB6 Form that runs it. The code is focusing on getting access to Form Controls in the same VB instance running it. That is not perticular useful in my case.

So far (if I've undestood it correctly) only Excel Workbooks support Accecibility via it's EXCEL7 class. Together with the parameter OBJID_NATIVEOMI and AccessibleObjectFromWindow it could get me the workbook object itself.

These's a lot of information about Accecibility and C++ code examples around. I don't understand the C++ or Delphi code examples I've found and they are more focused to get access to certain Form Objects like the Microsoft VB6 example, not the application object itself. I'm starting to feel like I'm reading the "same" text all over again, but something essential is missing. It might of course be my lacking knowledge of the English language but whatever it is I just can't get this thing going.

Any input on this matter, would be appreciated.

Sincerely, PeSi

 

[BrianB]

A couple of ideas

1. If you can bring the widow to the top of z-order you can then use Sendkeys to manipulate its menus to navigate the workbook. Perhaps copy something to the clipboard to get its value.

2. The root of the problem is that you are allowing users to do something outside the control of the project. Perhaps you can provide them a means of opening the workbooks they need from within the project, when you can make your own instances.
eg. File Open dialog.

 

[PeSi]

Hello again BrianB

The very few VB code examples I could find after days of searching all over the web was focused on getting some info about the controls belonging to a visible window or its "children". Very interesting, but completely irrelevant.

Finally I found a C++ code example that gave me the remaining pieces of information I needed to figure it out myself. Actually the code was more complicated than necessary.

I just finished testing my code with VB6, VBA5 and VBA6. It seems to work just fine. It doesn't even need a reference to the Accecibility Library. The "only extra" requirement is that "oleacc" is installed on the computer.

See the code in the next message.

 

[PeSi]

Sorry, I'm new around here — forgot to use the code button.

Here's the whole code again. I've already made some changes to it. Replaced Excel Object Tree references to Object type. That prevents errors if the FrontLoader is launched in a computer without Excel. I also made a function that can get the xlInstance based on a name of an open Workbook.

Use at your own risk :wink:

Sincerely, PeSi

'//Getting the Excel Instance of a specific WB.
'
'Developer: Pekka Simola
'
'Latest modified: Aug 19, 2007
'
'NOTES: • Tested to work with XL97 and XL2000 via VBA, and VB6.
'
Option Explicit
Private Const OBJID_NATIVEOM = &HFFFFFFF0
Type GUID
    lData1 As Long
    iData2 As Integer
    iData3 As Integer
    aBData4(0 To 7) As Byte
End Type
Private Declare Function FindWindowExA& Lib "user32" (ByVal hWnd1&, ByVal hWnd2&, ByVal lpsz1$, ByVal lpsz2$)
Private Declare Function AccessibleObjectFromWindow& Lib "oleacc" (ByVal hwnd&, ByVal dwId&, riid As GUID, xlWB As Object)

'//Returns Excel.Application object of the first running
'  xlInstance that contains a defined global name: sName.
'  Otherwise Nothing is returned.
'  REMARK: sName must not be a local xl name.
Function GetXlApp_ByDefName(sName$) As Object
    Dim WBobj As Object, xlApp As Object, WB As Object
    Dim IDispatch As GUID, xlWnd&, WBsWnd&, WBWnd&, S$
    
    If Len(sName) = 0 Then
        MsgBox "Invalid argument in GetXlApp_ByDefName", vbCritical, "PS"
        Exit Function
    End If
    
    SetIDispatch IDispatch
       
    On Error GoTo oops
    Do
        xlWnd = FindWindowExA(0, xlWnd, "XLMAIN", vbNullString) 'Next xl hWnd.
        If xlWnd = 0 Then
            Exit Do
        Else
            WBsWnd = FindWindowExA(xlWnd, 0&, "XLDESK", vbNullString) 'Get WB collection.
            WBWnd = FindWindowExA(WBsWnd, 0&, "EXCEL7", vbNullString) 'Get any WB.
            
            If WBWnd Then
                Set WBobj = Nothing
                Call AccessibleObjectFromWindow(WBWnd, OBJID_NATIVEOM, IDispatch, WBobj) 'Get WB object.
                If Not WBobj Is Nothing Then
                    Set xlApp = WBobj.Application 'Excel.Application of WBobj.
                    For Each WB In xlApp.Workbooks
                        S = WB.Names(sName).RefersTo
                       'At error try next WB.
                       'Otherwise return curr xlApp.
                        Set GetXlApp_ByDefName = xlApp
                        Exit Do
NextWB:
                    Next WB
                End If
            End If
        End If
    Loop
    
    Set WBobj = Nothing
    Set WB = Nothing
    Set xlApp = Nothing
    
Exit Function
oops: Resume NextWB
End Function

'//Returns Excel.Application object of the first running
'  xlInstance that has opened the workbook sWBNameOrPath.
'  Otherwise Nothing is returned.
Public Function GetXlApp_ByWBName(ByVal sWBNameOrPath$) As Object
    Dim WBobj As Object, IDispatch As GUID, xlWnd&, WBsWnd&, WBWnd&
    
    sWBNameOrPath = Extract_FileName(sWBNameOrPath) 'File name (Case Insensitive).
    If Len(sWBNameOrPath) = 0 Then
        MsgBox "Invalid argument in GetXlApp_ByWBName", vbCritical, "PS"
        Exit Function
    End If

    Do
        xlWnd = FindWindowExA(0, xlWnd, "XLMAIN", vbNullString) 'Next xl hWnd.
        If xlWnd = 0 Then
            Exit Do
        Else
            WBsWnd = FindWindowExA(xlWnd, 0&, "XLDESK", vbNullString) 'Get WB collection.
            WBWnd = FindWindowExA(WBsWnd, 0&, "EXCEL7", sWBNameOrPath) 'Get WB.
            If WBWnd Then
                SetIDispatch IDispatch
               'Get the workbook object via WB hWnd and return its xlInstance.
                Call AccessibleObjectFromWindow(WBWnd, OBJID_NATIVEOM, IDispatch, WBobj)
                Set GetXlApp_ByWBName = WBobj.Application
                Exit Do
            End If
        End If
    Loop
    Set WBobj = Nothing
End Function

Private Sub SetIDispatch(ByRef ID As GUID)
   'Defines the IDispatch variable. The interface
   'ID is {00020400-0000-0000-C000-000000000046}.
    With ID
        .lData1 = &H20400
        .iData2 = &H0
        .iData3 = &H0
        .aBData4(0) = &HC0
        .aBData4(1) = &H0
        .aBData4(2) = &H0
        .aBData4(3) = &H0
        .aBData4(4) = &H0
        .aBData4(5) = &H0
        .aBData4(6) = &H0
        .aBData4(7) = &H46
    End With
End Sub

'Return text after the last \ / or : or sPath.
Public Function Extract_FileName$(ByRef sPath$)
Extract_FileName = Mid$(sPath, InStrRev_DirSeparator(sPath) + 1)
End Function

'Should work with most OSs, / = “Unix/Internet” path separator.
Function InStrRev_DirSeparator%(sPath$)
    InStrRev_DirSeparator = InStrRev(sPath, "\")
    If InStrRev_DirSeparator = 0 Then InStrRev_DirSeparator = InStrRev(sPath, "/")
    If InStrRev_DirSeparator = 0 Then InStrRev_DirSeparator = InStrRev(sPath, ":")
End Function

:wink: