Open 1 session of Excel from Access - currently getting multiples

[Guest]

I'm using the following code to open Excel and a file(s). The user can then
run another query which would again call the procedure containing this code
and I end up with another session of Excel open. I would like to check for
an open Excel session and use that session if it is open. How can I best
accomplish this?

Dim XL As Excel.Application
Set XL = CreateObject("Excel.Application")

XL.Visible = True ' make Excel visible

If strExcelFileName <> "" Then
XL.Workbooks.Open FileName:=strExcelFileName
End If

 

[solex]

This sample uses early binding, if you need to use late binding then replace
the declaration Excel.Application with Object and instead of Set exl = New
Excel.Application use Set exl = CreateObject("Excel.Application")


Public Property Get Application() As Excel.Application

Dim exl As Excel.Application

On Error GoTo ErrorHandler

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

ExitHandler:
Set Application = exl
Exit Property
ErrorHandler:
If exl Is Nothing Then
Set exl = New Excel.Application
End If
Resume ExitHandler
End Property

 

[Guest]

Thanks for the reply. As per my other post (sorry, about that - learning
curve here - I posted the same question twice in error). I've tried to
follow the GetObject suggestions and I'm getting err number 432, "File name
or class name not found during automation operation. I know the file I'm
trying to open does exist.

 

[Guest]

I also tried using late binding vs early binding and in that scenario I get
the message Automation error - The message filter indicated that the
application is busy. ps. Any tips regarding knowing when to use late vs
early binding would be appreciated.

 

[solex]

Mo,
Make sure you copy the code explicitly and not the comman before the class
name in the GetObject command. I use this as a property of function to get
an available instance of excel and then open the appropriate file. In my
case I open a new workbook as shown below:

Dim xlApp As Excel.Application
Dim xlWkb As Excel.Workbook


Set xlApp = ExcelHandler.Application '<=== This is the function/property
listed below
xlApp.Visible = False

Set xlWkb = xlApp.Workbooks.Add()