Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 883
  • Last Modified:

Get control of other Excel Application from an existing one

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
PiyusKanti
Asked:
PiyusKanti
  • 6
  • 6
  • 4
  • +1
1 Solution
 
darbid73Commented:
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
 
PiyusKantiAuthor Commented:
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
 
PiyusKantiAuthor Commented:
The new workbook name is Book1
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
zorvek (Kevin Jones)ConsultantCommented:
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
 
darbid73Commented:
<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
 
PiyusKantiAuthor Commented:
Hey Kevin,

I'm not instantiating the new excel application. It is done by the external application which i'm talking about.
0
 
zorvek (Kevin Jones)ConsultantCommented:
>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
 
PiyusKantiAuthor Commented:
Kevin,

Thanks for the details. Still let me know if you find anything.
0
 
PiyusKantiAuthor Commented:
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
 
darbid73Commented:
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
 
PiyusKantiAuthor Commented:
Hey it worked. Thanks a lot.
0
 
nffvrxqgrcfqvvcCommented:
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
 
zorvek (Kevin Jones)ConsultantCommented:
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
 
nffvrxqgrcfqvvcCommented:
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
 
zorvek (Kevin Jones)ConsultantCommented:
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
 
nffvrxqgrcfqvvcCommented:
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
 
zorvek (Kevin Jones)ConsultantCommented:
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
 
nffvrxqgrcfqvvcCommented:
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
 
zorvek (Kevin Jones)ConsultantCommented:
GetObject requires the entire path to the workbook if the workbook has been saved to disk.

Kevin
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 6
  • 6
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now