Unable to close Excel app with macro

[Guest]

I use the following code in Access to open an instance of Excel to do some
worksheet operations and close Excel at the end:

Dim appXL As Excel.Application
Set appXL = CreateObject("excel.application")
appXL.Visible = True
.... code to open excel file, do some simple operations, save file ...
appXL.Quit
Set appXL = Nothing

When the macro is done, it seems like Excel closed normally (gone from task
bar), but in fact there's still an instance of Excel in the Process tab of
Task Manager (Application tab shows no Excel).

If I run the same macro again the second time, it basically crashes at the
first line after opening the spreadsheet file. I get the message:
Run-time error '1004': Method 'Cells' of object '_Global' failed
And I see 2 Excel listed in Process tab of Task Manager, with only 1 Excel
in Application tab, also 1 Excel in taskbar at the bottom of screen.

I did notice that if I press the Reset button in the VBA window after
running the macro, that Excel instance in Task Manager would truely exit.
After that I can run the same macro a second time with no problem.

That means I would need to press the Reset button each time after running
the macro, if I wanted to run the same macro again without crashing. I can't
expect the user to do the same thing. So I would like to know what's causing
that problem and if there's a way around it.

By the way, I am using Office 2003. It would also be helpful to know if
others with the same version have the same problem. If I'm the only one then
maybe it's a problem with my system rather than code problem. Thanks for
helping.

-Simon

 

[Ken Snell \(MVP\)]

That's because you are creating two instances of EXCEL with this code. Try
this:

Dim appXL As Object
Set appXL = CreateObject("excel.application")
appXL.Visible = True
.... code to open excel file, do some simple operations, save file ...
appXL.Quit
Set appXL = Nothing

 

[Ken Snell \(MVP\)]

Additionally, depending upon the actual code steps you are using in the
"..." area, you may be creating additional references to objects that are
created via the EXCEL application. You'll need to give us details about the
"not shown" code for us to give comments/insight about that possibility.

 

[Guest]

Hi Ken,

I tried your suggestion and still got the same problem. I even tried a
different way where I replace the top two lines with just one line of:
Dim appXL As New Excel.Application 'without the createobject line at all
But even that gave me the same error.

Also, I have tried running the macro with breaks to each line. Checking
Task Manager on each step, and I only see one instance of Excel throughout
the whole thing. But of course that instance doesn't disappear at the end of
it, and a second instance appears when I run again.

Please let me know if you can think of something else that might help.
Thank you.

-Simon

 

[Ken Snell \(MVP\)]

As I noted in a separate reply, we'll need to see all the code for what
you're doing to manipulate the EXCEL file's contents.

 

[Guest]

Sorry for not getting back to you on the same day. Below is my complete sub.

Private Sub cmdImport_Click()

Dim appXL As New Excel.Application
appXL.Visible = True

Dim strOpenFile As String
strOpenFile = appXL.GetOpenFilename("Excel Files (*.xls), *.xls", , "Select
..xls file")

If strOpenFile = "False" Then
appXL.Quit
Exit Sub
End If

appXL.Workbooks.Open strOpenFile

Range("J:J,L:L,O:U,W:W,Z:AB,AD:AD").Select
Selection.Replace What:=" ", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
ReplaceFormat:=False

appXL.ActiveWorkbook.SaveAs Left(strOpenFile, Len(strOpenFile) - 4) &
"Simon.xls"
appXL.Quit
Set appXL = Nothing

End Sub

As you can see, there's only one action done to the excel file for now.
Additional steps will be added in there if I can get pass this problem I
have. This macro starts by clicking a command button in the Access form.
Thanks for your help.

-Simon

 

[Ken Snell \(MVP\)]

Using ActiveWorkbook, and using Range and Selection (without an object
reference) creates a new reference to the workbook file that keeps the
instance of EXCEL running.
Try this:

Private Sub cmdImport_Click()

Dim appXL As New Excel.Application
Dim XLwb As Object
appXL.Visible = True

Dim strOpenFile As String
strOpenFile = appXL.GetOpenFilename("Excel Files (*.xls), *.xls", , "Select
..xls file")

If strOpenFile = "False" Then
appXL.Quit
Exit Sub
End If

Set XLwb = appXL.Workbooks.Open(strOpenFile)

XLwb.Range("J:J,L:L,O:U,W:W,Z:AB,AD:AD").Select
XLwb.Selection.Replace What:=" ", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
ReplaceFormat:=False

XLwb.SaveAs Left(strOpenFile, Len(strOpenFile) - 4) & "Simon.xls"
XLwb.Close
Set XLwb = Nothing
appXL.Quit
Set appXL = Nothing

End Sub

 

[Guest]

That was exactly the problem. I'm glad I learned something new. I was being
lazy before and figured whatever shortest that didn't give syntax errors
would work fine. Now I'll know to be more careful with object references.
Thank you so much for solving my problem.

-Simon