Solved

Get control of other Excel Application from an existing one

Posted on 2011-02-23
19
846 Views
Last Modified: 2012-05-11
I'm running a VBA code on an external application from excel. Now after clicking on 'export to excel' button on that application, the data is exported to a new workbook in a new excel application.
Please let me know how to get control over the new excel application or workbook.
0
Comment
Question by:PiyusKanti
  • 6
  • 6
  • 4
  • +1
19 Comments
 
LVL 19

Expert Comment

by:darbid73
ID: 34959863
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.
0
 
LVL 1

Author Comment

by:PiyusKanti
ID: 34959890
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.
0
 
LVL 1

Author Comment

by:PiyusKanti
ID: 34959893
The new workbook name is Book1
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 34959913
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
0
 
LVL 19

Expert Comment

by:darbid73
ID: 34959943
<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.
0
 
LVL 1

Author Comment

by:PiyusKanti
ID: 34959945
Hey Kevin,

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

Expert Comment

by:zorvek (Kevin Jones)
ID: 34959990
>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
0
 
LVL 1

Author Comment

by:PiyusKanti
ID: 34960188
Kevin,

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

Author Comment

by:PiyusKanti
ID: 34960203
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

Open in new window

0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 19

Accepted Solution

by:
darbid73 earned 500 total points
ID: 34960235
I agree that through VBA you could get all the running prosses and check if it is excel but that is it.

But using the windows api you could do this and get the handle to each excel.  Then get your object from that.


what is missing is the rest of the code. See Here
0
 
LVL 1

Author Comment

by:PiyusKanti
ID: 34960720
Hey it worked. Thanks a lot.
0
 
LVL 29

Expert Comment

by:nffvrxqgrcfqvvc
ID: 34960751
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.
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 34963800
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
0
 
LVL 29

Expert Comment

by:nffvrxqgrcfqvvc
ID: 34985059
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.
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 34985169
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
0
 
LVL 29

Expert Comment

by:nffvrxqgrcfqvvc
ID: 34985223
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.

0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 34986246
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
0
 
LVL 29

Expert Comment

by:nffvrxqgrcfqvvc
ID: 34988046
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?
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 34988099
GetObject requires the entire path to the workbook if the workbook has been saved to disk.

Kevin
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now