RE: weird, hard to find Excel-automation error

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 spread*** open, you
don't close their spread*** 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 xl*** = 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


"Jesper Fjølner" wrote:

> 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
>
>
>
>
.