Excel Automation Code Fails To Quit Excel

[MikeC]

I have written a procedure containing automation code for Excel 2002. The
problem is that Excel keeps running in background after the procedure closes
the workbook and "quits" Excel. Excel continues to lock the workbook file
until after I either kill Excel via the Task Manager or quit Access.
Otherwise, the code works as intended.

I have read many posts on the subject of using Excel via automation code and
have applied every relevant technique I could find. Does anyone know of a
different way to re-write the problem line of code (indicated below) so that
I can avoid the problem where Excel remains running in background?

The below code is being run from a standard code module in Access 2002. The
operating system is Windows XP.

Private Sub FormatXLWSColumn(pstrFilePath As String)
On Error GoTo ERR_HANDLER

'The automation code contained in this procedure is used to format one
of the columns
'in an Excel 2002 workbook.

'The pstrFilePath argument contains the fully qualified path to an Excel
workbook, including the file name, on
'the local C drive.

Dim objXLApp As Excel.Application
Dim objXLWB As Excel.Workbook

Set objXLApp = New Excel.Application

Set objXLWB = objXLApp.Workbooks.Open(pstrFilePath)

'vvvvvvv *Problem appears to be caused by the next line.* vvvvvvv
objXLApp.Rows("1:1").Find(What:="CurrBudget", After:=ActiveCell,
LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext,
_
MatchCase:=False, SearchFormat:=False).Activate

objXLApp.Columns(ActiveCell.Column).NumberFormat = "$#,##0.00"

EXIT_PROCEDURE:
On Error Resume Next
objXLWB.Close True
Set objXLWB = Nothing
objXLApp.Quit
Set objXLApp = Nothing
Exit Sub

ERR_HANDLER:
<Snip>
Resume EXIT_PROCEDURE

End Sub

 

[Ken Snell [MVP]]

When you use "ActiveCell", ACCESS creates a new EXCEL object because you're
not clarifying that it is to be part of the current object path.

You'll need to declare it with something like this:
After:=objXLWB.Worksheets(1).ActiveCell

 

[MikeC]

Thanks Ken.

Your suggestion pointed me in the right direction. It turned-out that I
needed to replace both instances of "ActiveCell" with
"objXLApp.ActiveCell".