Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Get control of other Excel Application from an existing one

Posted on 2011-02-23
19
Medium Priority
?
874 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 6
  • 4
  • +1
19 Comments
 
LVL 20

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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 20

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
 
LVL 20

Accepted Solution

by:
darbid73 earned 2000 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

721 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