Get control of other Excel Application from an existing one

how exactly are you doing this "Now after clicking on 'export to excel'"?

Or what unique things identify the new workbook / application - like names etc.

There is a button on the external application. When i click on the button some data is exported to a new workbook in a new excel application.

The new excel application is just like opening a new excel application with Book1.

The new workbook name is Book1

Assuming you are instantiating the Excel application, you already have the application object in your possession.

If you are not instantiating the Excel application then you cannot do what you want. There is no way to reliably get the Excel application automation instance.

Kevin

<There is a button on the external application. When i click on the button some data is exported to a new workbook in a new excel application.

The new excel application is just like opening a new excel application with Book1.>

So there is another application that you do not see the code of?  This would be the best way of getting the workbook - do you have access to this code.  If this is not possible then

1.  how is this new workbook uniquely identified - name - contents - age or something.

Book1 could be anything.

Hey Kevin,

I'm not instantiating the new excel application. It is done by the external application which i'm talking about.

>I'm not instantiating the new excel application. It is done by the external application which i'm talking about.

There is no known way to get a collection of all open Excel instances as automation objects for the following reasons:

- The GetObject function uses the ROT or Running Object Table to obtain the object but the ROT only contains the first executed instance (http://support.microsoft.com/kb/238975).

- Although it is possible to get process IDs and window handles for all Excel instances, it is impossible to convert a process ID or window handle into an automation instance.

- More specifically, it is not possible to obtain a file path given a window handle.

- While it is possible to find all open file handles for a process ID, perusing all of those file handles looking for handles to what might be open workbooks would be a tedious task.

Kevin

Kevin,

Thanks for the details. Still let me know if you find anything.

I'm using the following code. But there is something missing i think. Can you guys look at it and help.

Declare Function IIDFromString Lib "ole32" (ByVal lpsz As Long, ByRef lpiid As UUID) As Long
Declare Function AccessibleObjectFromWindow Lib "oleacc" (ByVal hwnd As Long, ByVal dwId As Long, ByRef riid As UUID, ByRef ppvObject As Object) As Long
Type UUID 'GUID
    Data1 As Long
    Data2 As Integer
    Data3 As Integer
    Data4(7) As Byte
End Type
Const IID_IDispatch As String = "{00020400-0000-0000-C000-000000000046}"
Const OBJID_NATIVEOM As Long = &HFFFFFFF0

Public Function GetExcelObjectFromHwnd(ByVal hwnd As Long) As Boolean

    Dim fOk As Boolean
    fOk = False
    Dim iid As UUID
    Call IIDFromString(StrPtr(IID_IDispatch), iid)
    
    Dim obj As Object
    If AccessibleObjectFromWindow(hwnd, OBJID_NATIVEOM, iid, obj) = 0 Then 'S_OK
        Dim objApp As Excel.Application
        Set objApp = obj.Application
        Debug.Print objApp.Workbooks(1).Name
        Dim myWorksheet As Worksheet
        For Each myWorksheet In objApp.Workbooks(1).Worksheets
            Debug.Print "     " & myWorksheet.Name
            DoEvents
        Next
        fOk = True
    End If
    GetExcelObjectFromHwnd = fOk

End Function

Select allOpen in new window

Hey it worked. Thanks a lot.

Here is original thread http:Q_25394641.html

You can use EnumWindows and EnumChildWindows but the hWnd must be that of a valid workbook. You can test code without enumeration by using Spy++ on the workbook once you get that working you could add code to find all workbook handles.

Please note that the technique above relies on the window title. It is very possible to have an instance of Excel already open with a window named "Book1" and then you're screwed. In other words the solution is not reliable and will fail under certain circumstances.

Now, what you could do is get a list of ALL Excel instances that have a window named "Book1" and then look at each workbook for signs that it is THE workbook for which you are looking, such as a specific header. But even then you can run into problems if it's yesterday's version of the same report.

Kevin

I agree the OP logic could fail depending on how he filters for the workbook needed but the actual enumeration is using the classname of the workbook not the window title. This is why using EnumWindows and finding all XLMAIN classnames will give you the main window handle to each excel instance and then using EnumChildWindows() and checking for the classname EXCEL7 would give you the handle to the workbook. You must pass the handle of the workbook for it to return you the dispatch pointer to a workbook object. Then from the workbook object you can request the Application object. How you filter after obtaining the object would require you know some more knowledge about how excel is being used.

Inside the workbook window internally there is a message loop that checks for it to recieve WM_GETOBJECT and OBJID_NATIVEOM when it detects that AccessibleObjectFromWindow() has sent these parameters to the workbook it uses LresultFromObject() and exposes the existing COM interface and marshals it back to the caller.

All well and good. But the problem is actually much simpler as I stated above. Two different instances of Excel can have the same window title such as "Book1". How do you distinguish one from the other?

Kevin

Hello Kevin,

I don't do much office automation myself but the workbook window has class name the class name is EXCEL7 so the title isn't actually being used to get the handle to the workbook rather the classname of the workbook window is used so the title doesn't matter for actually obtaining the COM interface. The next part as above is where I agree the OP's logic could fail depending on how he filters for the specific instance he needs which would require some more knowledge of how excel is being used.

Again, the issue is not how to obtain all the automation object handles. It's distinguishing one workbook from others.

There is actually an easy way to get  the automation object handle of the workbook and the owning application...if you know the window title:

   Dim TargetWorkbook As Excel.Workbook
   Dim TargetApplication As Excel.Application
   Set TargetWorkbook = GetObject("Book1")
   Set TargetApplication = TargetWorkbook.Parent

Works like a charm and no API calls were made ;-)

The OP knows the title of the workbook: Book1.

The problem is that there could be other instances of Excel open with the same window title.

Perusing system tables doesn't really provide much additional benefit.

Kevin

So I installed Excel 2000 anchient! When I open an instance it defualts to Book1. If I open a second instance and leave the first instance un-touched the new instance opens with Book2. The GetObject() code worked by specifying the title but doesn't work if you save the Book. The title then becomes Book1.xls or Book1.xls [Read Only]. GetObject() fails. The GetObject() example is very different than the enumeration example above which doesn't use the title of the workbook to obtain COM interface pointer. So do you know if newer versions of office are different than default settings in 2000?

GetObject requires the entire path to the workbook if the workbook has been saved to disk.

Kevin