Getobject with Excel creates new instance when already running

[Rick]

In order to generate an Excel report from a query, I've got code to use a
current instance of Excel if it's running already. If Excel isn't running,
then it creates a new instance of Excel. This happens frequently. Here's my
code:

On Error GoTo ErrorHandler

Set db = CurrentDb
Set rst = db.OpenRecordset("qryExcelTemplate")

Set objApp = GetObject(, "Excel.Application")
Set objWorkbook = objApp.Workbooks.Open("C:\Noodle!\Report template.xls")
Set objWorksheet = objWorkbook.Worksheets("Report")

....

ErrorHandler:
If Err.Number = 429 Then
'Excel is not running, open Excel with CreateObject
Set objApp = CreateObject("Excel.Application")
Resume Next
Else
MsgBox "Error No: " & Err.Number & "; Description: " * Err.Description
Resume ErrorHandlerExit
End If

 

[Rick]

Sorry, forgot to state the problem.

The problem is that my code creates a new instance of Excel at times when
Excel is already running. And it creates the new instance at the GetObject
line, not the CreateObject line. The reason I know it's a new instance is
that when I close the entire Excel App with the new report, the existing
Excel continues to stay open.

 

[Klatuu]

That may not be the problem. The most common cause of an instance of Excel
left when you quit the Excel app is incomplete object referencing. In other
words, some where in your code it is likely you are creating an Excel object
and Access doesn't know which application object it belongs to, so it will
create an addition object on its own. Since the Excel objects are external to
Access, you have to be careful to fully qualify every Excel object you use.

 

[Rick]

Thanks!

That may not be the problem. The most common cause of an instance of Excel
left when you quit the Excel app is incomplete object referencing. In other
words, some where in your code it is likely you are creating an Excel object
and Access doesn't know which application object it belongs to, so it will
create an addition object on its own. Since the Excel objects are external to
Access, you have to be careful to fully qualify every Excel object you use.

 

[david]

There is a specific com api sequence by which objects register
themselves when they open. Some objects don't register, or
don't always register, or only register when asked to, or ...

It the object is open, but not registered in the open objects list,
there is no way that GetObject will ever notice it. I have certainly
had this problem with Excel and Word in the past. One of those
normally registered itself when opened by a user, but not when
opened by CreateObject.

I hope that you are not having this problem. If you are lucky,
the problem you are having is that Excel is not closing/shutting
as expected. That is a much easier problem to fix, and can
be tested by opening, then immediatly closing the file. If that
always works, you having a shutting problem, not an opening
problem, and you can find the problem by working out what
it is you are doing that causes Excel to not close/shut as expected.

(david)