Problem opening excel 2007 in new instance using VBA (using OLE and bound object frame)

Question

Hi Guys,

I have problem creating new instance of excel 2007 using VBA (from Access 2002).

At first I misunderstood the problem and it is more complicated than I thought.

Set myXL = CreateObject("Excel.Application")

Creates new processes (I thought it didn't), but still strange thing is happening. I use OLE to store Excel files in SQL Server databse.

This is the function that bounds excel file to a form:

Public Function OpenExcelObjFrame() As BoundObjectFrame
 Dim myXL As Object

 Set myXL = CreateObject("Excel.Application") '#1

 Set gTempWorkbook = myXL.Workbooks.Add
 myXL.Visible = True         '#2

 DoCmd.OpenForm "Excel_OBJ"
 Forms("Excel_OBJ").Visible = False
 Forms("Excel_OBJ").RecordSource = "SELECT Excel_File FROM Excel_File_Obj;"
 Set OpenExcelObjFrame = Forms("Excel_OBJ").Excel_File
End Function

The context:

Dim tExcelObjFrame As BoundObjectFrame
Set tExcelObjFrame = OpenExcelObjFrame()

tExcelObjFrame.Verb = acOLEVerbOpen
tExcelObjFrame.Action = acOLEActivate '#3

ad 1 2nd process excel.exe starts

ad 2 Added this line to check what is happening to the 2nd process

ad 3 OLE bounds the file to existing excel instance, the 2nd process is killed here :/

So the problem is:

1. Why the tExcelObjFrame.Action = acOLEActivate kills the 2nd process

2. How to force Object Frame to activate excel file in the 2nd process

EDIT

I've found partial explanation here (with no clear solution):

http://www.xtremevbtalk.com/showthread.php?t=292170

1. If no instance of Excel is running, OLE creates an instance of Excel when you access the object, either directly or through programmation.
2. If an instance of Excel is already running, then this is the instance that is used by the OLE object.

The problem is that OLE object chooses wrong excel instance. Question is if there is a way to point OLE to a specific instance.

Same problem described here (no solution given):

http://groups.google.com/group/microsoft.public.win32.programmer.ole/browse_thread/thread/9c4cde2a79453037

Answer 1

Update:
I've been trying your existing code on my workstation and the:

Set myXL = CreateObject("Excel.Application")

seems to works as you're requesting.
I've had one process open, then I run that line and it creates another process.

I've been investigating a little further and it turns out that this is a bit of a quirk of Excel 2007.
Everything I've been looking at leads me back to windows file type (scroll right to the bottom) or registry hacks, which are basically changing the same thing in this situation.

I know that in VBA you can play with registry keys with CreateNewKey and SetKeyValue, but I'm not sure that you'll want to do that.

Answer 2

I had similar problems just when trying to open multiple workbooks in individual Excel 2007 instances - in Excel 2003 this worked fine, but now they all open in the same instance.

I don't know if this will solve your problem too, but I fixed it (or made it work the way I wanted!) by telling Excel not to use DDE (Options, Advanced, General, Ignore other applications that use DDE). Note, if you do this you'll need to change all of the file types in Explorer that start Excel as they use DDE by default - edit the file options, uncheck "Use DDE" and add "%1" to the end of the command line.

(actually, I think that might answer a comment from Andy's answer, where only one Excel instance was being created, but not necessarily help with the original question, where multiple instances are opened)