How do you use GetObject() when more than one instance of application is open?
Stephen M. Rasey
In doing some Access-> Excel and PPT -> Excel inter-Office application
development, I commonly need to get the Excel Object from Access or PPT and
then interegate application object to determine what workbooks are open.
Function smrGetXLfunc() As Application
'Return an Excel Application that is already open
'to be put into the calling program.
'appxl must be a global variable
On Error Resume Next
Set smrGetXLfunc = GetObject(, "excel.Application")
If Err <> 0 Then
MsgBox "Could not find Excel object"
End If
End Function
...
<Main>
Set appXL = smrGetXLfunc()
For each wb in appXL.workbooks
'search for workbook names or properties to find the ones of
interest.
'then do some magic.
Next
<End example>
One problem I have is when I have more than one instance of Excel open on my
task bar. The Windos Task Manager shows two or more EXCEL images with
different PID's.
The GetObject(,"Excel.Application") returns one of these Excel images as an
application object, but it may not be the one that contains the open
workbooks I wish to investigate.
How can I investigate other running instances of an Excel application (or
Access, or PPT, or any other Office app?)
Is there a way I can get something like a collection of instances of an
application and cycle through the collection until I find the one with a
desired type of workbook.
Why don't I do a GetObject("Desired\File.xls")? I will not know the name
of the file before attempting to access the application. I need to get
access to the application object and then cycle through all open workbooks
looking at wb.names("Keyword") for Workbooks of a specific type I wish to
process. Only after found can I do a wb.name and wb.Path to identify the
filename.
Is there a hiddent parameter to GetObject to get an instance? Is there
key work other than "Excel.Application" Is there a WinAPI that does the
job?
Thanks in advance.
Stephen Rasey
Houston
Patrick Molloy
will show onle one Excel app, irrespective of how many workbooks you have
open in it.
Also if in VB you create an instance of excel, when you've finished,
remember to quit the instance and release memeory...
dim xlApp as excel.application
set xlApp = New Excel.Application
......
xlAll.quit
set xlApp=Nothing
--
Patrick Molloy
Microsoft Excel MVP
"Stephen M. Rasey" <ras...@texaco.com> wrote in message
news:#7#kc#RaAHA.1380@tkmsftngp04...
Stephen M. Rasey
I actually have two or more instances of Excel running as seen in the
Windows NT Task Manager, each with its own PID. This is not a case of
several workbooks or windows open in XL2K each showing up on the task bar.
(I do appreciate that tip about Tools Options, Windows in Taskbar check box.
I did not know about that one.)
Setting the App to nothing on exit is good advice but that is not the cause
of my problem. It does illustrate what might happen, however.
I might have an Excel workbook open when an Access Application or Word or
PPT perform a CreateObject("Excel.Applicaiton"). Then I have more than
one Excel instance running. Now a GetObject(,"Excel.Application") will
return one of those instances, but which one is an open question.
Another way the problem rears its ugly head is if Excel crashes. If I
restart Excel and look at the Task Manager, I may see two excel instances
running.
And then there are cases when I have an Excel session running a Web Query
refresh application and I want another independently running Excel session
running some other process that want to run simultaneously with the Web
Query running "in background".
So there are at least three legitimate times when I may have more than one
Excel instance (i.e. thread) running and GetObject() often fails to return
the instance I want.
The problem is even more obvious with Access. If you want to open two or
more Access databases at once, you must open a new instance of Access for
each one. How can you determine what databases a user might have open if
GetObject() only returns one out of many Access Application Objects?
If I am not off base, doesn't Word 2000 also open a new instance of the Word
Application for each document? I remember something like this from Woody's
Office Watch which took Microsoft to task for violating their own Multiple
Document Interface conventions.
Stephen Rasey
Houston
(Running WinNT 4 SP6, Office 97)
Patrick Molloy wrote in message <#IVuyPSaAHA.1908@tkmsftngp04>...
>in Tools Options you may have Windows in Taskbar checked. uncheck it. This
>will show onle one Excel app, irrespective of how many workbooks you have
>open in it.
>
>Also if in VB you create an instance of excel, when you've finished,
>remember to quit the instance and release memeory...
>dim xlApp as excel.application
>set xlApp = New Excel.Application
>......
>xlAll.quit
>set xlApp=Nothing
>
>--
>Patrick Molloy
>Microsoft Excel MVP
>
>
>"Stephen M. Rasey" <ras...@texaco.com> wrote in message
>news:#7#kc#RaAHA.1380@tkmsftngp04...
....
Peltier
I'm not saying I could do this, but I would try to investigate the
window handle APIs. Try vbapi.com, which devotes many html pages to VB
treatment of APIs. There must be a way to loop through the open
windows, loading them into an array, then picking out each one's class.
And if you figure it out, could you post the solution here? I'm sure
many of us could find a use for it.
Good luck,
- Jon
_______
Stephen M. Rasey
http://support.microsoft.com/support/kb/articles/Q258/5/11.asp
<Clip>
Find the Window Handle for an Application That Can Have Multiple Instances
Some applications, such as Microsoft Excel or Microsoft Word, can have
multiple instances running at the same time. To retrieve the handle to the
application instance that you are automating,
>>>you can first use Automation to change the title of the application to a
unique value <<< [emphasis SMR]
and then use the FindWindow API function to retrieve its window handle. The
following steps illustrate this technique by using Microsoft Excel as the
Automation server.
Step-by-Step Example
Start a new Standard EXE project in Visual Basic. Form1 is created by
default.
Add a command button, Command1, to Form1.
Copy the following code to the Code window of the Form1 form:
Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" _
(ByVal lpClassName As String, ByVal lpWindowName As String) As Long
Private Sub Command1_Click()
Dim xlApp As Object
Set xlApp = CreateObject("Excel.Application")
xlApp.Caption = "New Caption Supplied by Program"
Dim hWndXl As Long
hWndXl = FindWindow("XLMAIN", xlApp.Caption)
xlApp.Caption = Empty 'Set the original caption back
xlApp.Visible = True
MsgBox "hWndXl ( " & Hex(hWndXl) & " ) contains the Window Handle of the
Excel application created by this program." & vbCr & _
"You can use this Window Handle in various Win 32 APIs, such as
SetForeGroundWindow," & vbCr & _
"which require a Window Handle parameter to be supplied." & vbCr &
vbCr & _
"All Done. Click OK to close Excel.", vbMsgBoxSetForeground
xlApp.Quit
Set xlApp = Nothing
End Sub
Press F5 to run the program. Click Command1. Excel starts and then a message
box appears indicating the window handle for Excel. Click OK to dismiss the
message box and quit Excel.
</Clip>
Change the name of the application? So, could you use a process where you
set appXL1 = GetObject(,"Excel.Application")
appxl1.Caption = "Excel01"
set appXL2 = GetObject(,"Excel.Application")
appxl2.Caption = "Excel02"
set appXL3 = GetObject(,"Excel.Application")
appxl3.Caption = "Excel03"
(with proper error detection of course>
Bizarre!
Stephen Rasey
Houston
Peltier
Thanks for the link. It isn't exactly what I had in mind, but it led in
turn to several others. I think the relevant one is "Q183009 HOWTO:
Enumerate Windows Using the WIN32 API."
http://support.microsoft.com/support/kb/articles/Q183/0/09.asp
The naming thing for Excel is bizarre. For fun, in my personal.xls
workbook_open code, I used to change the Excel caption to "Mighty
Excel." Well, my wife was doing a mail merge deal in Word, which used
data from an Excel spreadsheet, and bizarre things kept happening, like
Excel opened up three instances during the merge. Well, Word was
looking for an app called "Microsoft Excel." None of this "XLMAIN" or
class identifiers, no no, not for Microsoft's own products. And I
caught hell from the boss.
Live and learn.
- Jon
_______
Stephen M. Rasey
side effects. It is the type of thing you want to do very quickly get
your information and then return to original state ASAP.
I'll check out the Q183009 today.
Regardless, I think I'll experiment on my notebook. This does not sound
like something I should do on my production machine untill I know my
footing.
Steve Rasey
Houston
Peltier wrote in message <3A401602...@home.com>...