Open 1 session of Excel from Access - currently getting multiples

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mo - 13 Oct 2004 14:01 GMT

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

Reply to this Message

solex - 13 Oct 2004 16:24 GMT

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

> 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
[quoted text clipped - 10 lines]
> XL.Workbooks.Open FileName:=strExcelFileName
> End If

Reply to this Message

Mo - 13 Oct 2004 20:35 GMT

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.

> 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
[quoted text clipped - 35 lines]
> > XL.Workbooks.Open FileName:=strExcelFileName
> > End If

Reply to this Message

Mo - 13 Oct 2004 20:47 GMT

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.

> 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
[quoted text clipped - 41 lines]
> > > XL.Workbooks.Open FileName:=strExcelFileName
> > > End If

Reply to this Message

solex - 13 Oct 2004 20:59 GMT

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()

> 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
[quoted text clipped - 46 lines]
> > > > XL.Workbooks.Open FileName:=strExcelFileName
> > > > End If

Reply to this Message