weird, hard to find Excel-automation error

[Jesper Fjølner]

I'm sending some data to Excel, but I'm getting a couple of strange errors
intermittently.
Approximately every other time everything runs without problems and fails
every other time.
I have Access open up Excel with the code below. When I close the Excel
window it seems that "Excel.exe" remains in my task list (ctrl-alt-del).
So I suspect it has something to do with wrongful opening or closing of the
app, but can't locate it.

I'm using:

Dim objExcelApp As Object 'late binding
Dim objExcelDoc As Object 'late binding
Set objExcelApp = CreateObject("excel.application") 'late binding
Set objExcelDoc = objExcelApp.Workbooks.Add

Code usually fails at the first insert into a cell such as:
objExcelDoc.Sheets(1).range("A1").Value = "text"

I end the procedure with:
Set objExcelDoc = Nothing
Set objExcelApp = Nothing

The errors I get are:
Run-time error '462'
The remote server machine does not exist or is unvailable
or
Rune-time error '1004'
Method 'Range' of object '_Global' failed

The error newer occours on the first run, but always on the second and
subsequent.
If I close and reopen the database, it runs again on the first run and fails
on the subsequent.
Even though I terminate Excel.exe via the tasklist, the error doesn't go
away before I restart the database.
Can anyone point me in a direction?


Jesper F

 

[Klatuu]

The only real problem I see is that you do not do a Quit on the Excel
Application Object. You do have to be careful in referencing your objects.
If you instanciate an Excel object and Access is not sure what higher level
ojbect it belongs to, it can create another instance of Excel you don't know
about and it will stay resident and be seen in the processes tab of task
manager. Since yours is in the application tab, I suspect the Quit is the
problem. I have included three snipits of code. Once for creating a new
work book, one for closing it, and one that determines whether an Instance of
Excel was already running. This is so if a user has a spreadsheet open, you
don't close their spreadsheet when you quit the instance you created:

On Error Resume Next ' Defer error trapping.
Set xlApp = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
blnExcelWasNotRunning = True
Set xlApp = CreateObject("excel.application")
Else
DetectExcel
End If
Err.Clear ' Clear Err object in case error occurred.
On Error GoTo CreateWorkbook_Err
DoEvents
xlApp.DisplayAlerts = False
xlApp.Interactive = False
xlApp.ScreenUpdating = False
Set xlBook = xlApp.Workbooks.Add
***************************************************
xlBook.Close
If blnExcelWasNotRunning = True Then
xlApp.Quit
Else
xlApp.DisplayAlerts = True
xlApp.Interactive = True
xlApp.ScreenUpdating = True
End If
Set xlSheet = Nothing
Set xlBook = Nothing
Set xlApp = Nothing


***********************************************
Sub DetectExcel()
' Procedure dectects a running Excel and registers it.
Const WM_USER = 1024
Dim Hwnd As Long
' If Excel is running this API call returns its handle.
Hwnd = FindWindow("XLMAIN", 0)
If Hwnd = 0 Then ' 0 means Excel not running.
Exit Sub
Else
' Excel is running so use the SendMessage API
' function to enter it in the Running Object Table.
SendMessage Hwnd, WM_USER + 18, 0, 0
End If
End Sub

 

[Douglas J. Steele]

I've always used the following:

objExcelDoc.Close SaveChanges:=False
Set objExcelDoc = Nothing
objExcelApp.Application.Quit
Set objExcelApp = Nothing

As well, I believe Set objExcelDoc = objExcelApp.Workbooks.Add should
actually be

Set objExcelDoc = objExcelApp.Application.Workbooks.Add

 

[Ken Snell \(MVP\)]

You'll need to post all your code so that we can see what is being done.

 

[Klatuu]

I don't think this is necessary:
Set objExcelDoc = objExcelApp.Application.Workbooks.Add

objExcelApp is an application object.

I have been using it without the Application qualifer for some time and not
had any problems; however, If there is someone who knows that adding it is
beneficial, I would like to know.

 

[Douglas J Steele]

To be honest, I don't remember whether or not there's a benefit. In
responding to Jesper's post, I pulled out a column I'd written on Automation
for Smart Access, and that was the code I had in it. I actually did question
why it was there, given that we're instantiating objExcelApp as
Excel.Application, but I just looked through some projects I'd done in the
past, and they all have that .Application there.

Looking in the Excel help file just now, it recommends the .Application
keyword as well.

 

[Klatuu]

Interesting. To me it would be like

Dim dbf as Database
Dim rst as Recordset

Set dbf = CurrentDb
Set dbf.CurrentDB.rst = ...

Not disputing it. I just don't see what value it adds. Perhaps it could
help with avoiding ambiguity. Think I'll do some reading on this one.

 

[Douglas J Steele]

I'm not sure your example is really analogous.

Application is a property of the Excel Application object that returns a
reference to the Application object. (There's an Application object in the
Access model as well, but it doesn't have an Application property) On the
other hand, in your example, you've already declared dbf to be analogous to
CurrentDb: repeating it would be the same as CurrentDb.CurrentDb

For what it's worth, the Excel Help file also includes this line:

"Many of the properties and methods that return the most common
user-interface objects, such as the active cell (ActiveCell property), can
be used without the Application object qualifier."

 

[Klatuu]

Okay, the light comes on.
In that context, Application is a property of the application object.
objXLApp.Application

So it does make sense to use it.

However, as you point out, CurrentDb is a method of an Access Application
ojbect, so they are not the same. For some reason, I forget and keep
thinking of CurrentDb as an object rather than a method.
Doh!

 

[Jesper Fjølner]

Thanks for all your help everybody.
I got it working although I'm not excactly sure how.

I think the error was related to using:
..Range(Cells(i, 1), Cells(i, 5)).
instead of
..Range(.Cells(i, 1), .Cells(i, 5)).

inside a:
With objExcelDoc.Sheets(1)
....
End with
and thus me not referencing the active sheet (?). since the error only
occured on subsequent print out and not on the first one. I'm not completely
sure.
Thanks for all your comments.


Jesper

 

[Ken Snell \(MVP\)]

Very likely. Unless you fully qualify the Cells collection with the
objExcelDoc object, EXCEL will create a new reference to the worksheet, and
that will keep EXCEL running after you've "closed" it.

 

[Jesper Fjølner]

Very likely. Unless you fully qualify the Cells collection with the

objExcelDoc object, EXCEL will create a new reference to the worksheet,
and that will keep EXCEL running after you've "closed" it.

Phew that was tough to dig out. Thanks for the insight

Jesper