Getting a handle on open instances of Excel

[chuckschilling]

Hi all! I'm writing a VBA script which launches an Excel worksheet from within another application (Lotus Notes). The problem I'm having is that I want to lock down the script so that it only opens one instance of Excel, no matter how many times the user launches it. To do this, I'm pretty sure I'll need to get a handle on each instance of Excel that might be open (the user may or may not have Excel already running in another instance), and poll each instance of Excel for existing workbook names. The problem is that I don't know how to get a handle on each instance of Excel programmatically using VBA and don't know C or REXX or any of the other applications which might help me get a better handle on Office automation objects. Does anyone have any ideas as to how I can do this?

 

[FaneDuru]

You don't need a handle (I think...)
You may use:

Set OpenExcel = GetObject(,"Excel.Application")

for an existing instance and you will use:

Set NewExcel = CreateObject("Excel.Application")

for a new one.....

I hope this helps....
FaneDuru'

 

[xlbo]

So you can therefore use

On error goto New
Set OpenExcel = GetObject(,"Excel.Application&quot
goto AlreadyOpen

New:
Set NewExcel = CreateObject("Excel.Application&quot

AlreadyOpen:
On Error Goto 0
'carry on with code

Rgds, Geoff
Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?
Want the best answers to your questions ? faq222-2244

 

[chuckschilling]

Thanks for the responses, guys/gals . In actuality I do need to get a handle on each instance of Excel because I absolutely can have only one instance of my Excel worksheet (which is created from a Domino document) running because it is receiving contextual data from a local environment string and allowing multiples to be open could be catastrophic as each would then lose its reference to the parent Excel document and the data could not be transferred back.

I'm also not thrilled about the getObject(,Excel.Application) method because it defeats my suppression of display alerts and the client for the application is dead-set on not seeing these annoying Save popups since we don't actually save the Excel document anyway.

What I'd really like is a simple way of getting a handle on every open workbook on the user's workspace, regardless of the number of instances of Excel which are open. If the workbook name corresponds to the name of the sheet I'd be building, I'd not build a new one but instead activate the existing one. Has anyone done anything like this before?

 

[xlbo]

That's what my code example does. It tries the getobject and if that fails (ie they have no excel instances open), it creates an object....

Rgds, Geoff
Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?
Want the best answers to your questions ? faq222-2244

 

[virmaior]

once you have the excel object
just add this line of code to remove the "Save" messages



fixing xlbo's code:

Function myExcel() as Excel.Application
Dim x as object

On error goto New
Set x = GetObject(,"Excel.Application&quot
goto AlreadyOpen

New:
Set x = CreateObject("Excel.Application&quot
AlreadyOpen:
On Error Goto 0
x.displayalerts = false
x.screenupdating = false
set myExcel = x

End Function