Determining All Instances of Excel

[samadams_2006]

Hello,

I'm interested in determining all instances of Excel currently open on
my PC. I would then like to be able to loop through all of these
instances of Excel, and determine the Workbooks that are open in each
instance of Excel.

I understand that I can use something like the following code:

Dim myWB As Workbook
For Each myWB In Workbooks
MsgBox "Workbook: " & myWB.Name
Next myWB

to find all the workbooks in the current Excel Instance...but what
about all Excel Instances that are running on my PC.

Is there an easy way to do this?

Thanks
Sam Adams

 

[Peter T]

Hi Sam,

Is there an easy way to do this?

No, or rather putting something together to do that wasn't easy for me! The
problem is GetObject will return the first instance it finds. I gather with
C++ there's a method to return the 'Running Object Table' but I don't know
how to do that in VBA.

If this really important for you I can send you a workbook (code) that does
the following:

- Enumerate windows to find running Excel instances

- Also Enumerate windows to compile a list of all window captions named
BookX, (there will never be a duplicate unsaved BookX open), identify and
relate at least one BookX in each instance.

- If an instance doesn't contain an unsaved BookX use DDE (with Word) to
insert a new workbook and hide it for future use (don't know how to avoid
temporarily bringing the window to front to do that).

- Use GetObject to get one BookX from each instance or rather its parent
application.

Having got an array of WithEvents of each application gives pretty much the
same control over each as the instance containing the code. For my purposes
traps & stores changes to cells in all wb's in all instances, also a few
other things.

This is not trivial nor 100% reliable (though normally works). So as I
mentioned, unless you need this and you are relatively familiar with VBA I
wouldn't recommend it!

Regards,
Peter T
pmbthornton gmail com