Using An Already-Open Instance of Excel

[Guest]

I'm pretty conversant with using Excel automation from Access, but this
problem has me perplexed.

I'd like to populate a listbox in Access with a list of any Excel files that
happen to be open. However, my code doesn't seem to work:

Set objXL = CreateObject("Excel.Application") ' Line 1

For Each objWB In objXL.Workbooks

If objWB.IsAddin = False Then

lstFiles.RowSource = lstFiles.RowSource & objWB.Name & ";"

End If

Next

Set objXL = Nothing

lstFiles.Requery

End Sub

It appears that when I create my Excel object on line 1, a completely new
instance of Excel is created (I verified this by seeing two "EXCEL.EXE"
entries suddenly appear in the task manager), and this new instance isn't
aware of the workbooks already open by the original instance. In debug mode,
my code never enters the "For Each objWB In objXL.Workbooks" loop.

I get the same problem if I change line 1 to "Set objXL = New
Excel.Application

Does anyone know of a way around this problem? Or am I not approaching the
problem properly?

 

[Geoff]

To grab a running instance of another application, you need to use the
GetObject function. If the application is not running, you'll generate a
run-time error, which you need to trap. Example:

On Error Resume Next
Set objXL = GetObject(,"Excel.Application")
If Err.Number > 0 then
' Excel wasn't running so now use the CreateObject function
' if you need to start Excel; otherwise exit here.
End If

Geoff