Solved: multiple Application objects

[Sean.DiSanti]

I do most of my excel automation via COM from other languages, but i'm running into issues when there are multiple Application objects. For example if I have a workbook open, and instead of using File>Open to open another, i create another instance of excel and open a new workbook in that instance. Now there are 2 excel.Application's, each with a single element workbooks collection. How can i reference one workbook from the other?

[Bob Phillips]

Why not test for an existing instance, if there is one, use that, otherwise create a new instance.

[Sean.DiSanti]

I am doing that, but the thing is when there are multiple existing instances. The example given was just to create multiple instances, but the problem is when that condition already exists. Say you've got a word automation that interacts with a spreadsheet, and there are multiple instances of excel open. You can use GetObject("","excel.Application") to grab ahold of one instance. but if that particular instance isn't the one that your spreadsheet is open in, you can't use it to update info in the sheet. How would you go about specifying which instance to attach to with GetObject, or even get a collection of Application objects to loop through until the needed sheet is found?

[Sean.DiSanti]

Simple trick, just a pain in the butt to find. Instead of getting the object by class name like usual, if you use the filename of the workbook, you can get the workbook object, then go from there to get the specific open instance. like...

SET wbTheWorkbook = GetObject("C:\mywb.xls")
SET oAppInstance = wbTheWorkbook.Application'Tada!


***edit*** removed empty string as classname in the GetObject call, as it's not necessary in VBA, just in the other language i'm using today