Open 1 session of Excel from Access - currently getting multiples

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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
 
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.
 
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.
 
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()
 
Back
Top