Excel process doesn't close?

[Guest]

I am running Excel from MS Access. Basically I populate some cells from
Access data and then save and activate the workbook. After closing excel an
Excel.exe process remains in task manager. How do I get this to end and
still show the workbook?

Code is:

Dim appExcel As Object
Dim WBook As Object

'OPEN EXCEL

Set appExcel = CreateObject("Excel.Application")
Set WBook = appExcel.Workbooks.Open(strTemplateDir & strWBName)

Excel.Application.DisplayAlerts = False
With appExcel
.Range("A11").Value = Nz(Trim(rst![CustomerName]), " ")
.Range("A12").Value = Nz(Trim(rst![Address]), " ")
.Range("A13").Value = Nz(Trim(rst![City]), " ") & ", " & _
Nz(Trim(rst![State]), " ") & " " & Nz(Trim(rst![Zip]), " ")
.ActiveWorkbook.SaveAs filename:=strDocDir & strFileName, _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
.Visible = True
End With

ErrorHandlerExit:
rst.Close
Set rst = Nothing
Set WBook = Nothing
Set appExcel = Nothing

Exit Sub

 

[Guest]

try adding

ErrorHandlerExit:
rst.Close
WBook.Close False
appExcel.Quit

Set rst = Nothing
Set WBook = Nothing
Set appExcel = Nothing

 

[Tom Ogilvy]

Dim appExcel As Object
Dim WBook As Object

'OPEN EXCEL

Set appExcel = CreateObject("Excel.Application")
Set WBook = appExcel.Workbooks.Open(strTemplateDir & strWBName)

' Excel.Application.DisplayAlerts = False
appExcel.DisplayAlerts = False '<== changed
With appExcel
.Range("A11").Value = Nz(Trim(rst![CustomerName]), " ")
.Range("A12").Value = Nz(Trim(rst![Address]), " ")
.Range("A13").Value = Nz(Trim(rst![City]), " ") & ", " & _
Nz(Trim(rst![State]), " ") & " " & Nz(Trim(rst![Zip]), " ")
.ActiveWorkbook.SaveAs filename:=strDocDir & strFileName, _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
.Visible = True
End With

ErrorHandlerExit:
rst.Close
WBook.close SaveChanges:=False '<== added
appExcel.Quit ' <== added
Set rst = Nothing
Set WBook = Nothing
Set appExcel = Nothing

Exit Sub

Might help.

If that doesn't work, then the next step might be to do away with the with
statement and qualify each individual command

 

[Guest]

Thanks Patrick and Tom,

I tried both your ideas one at a time.

Closing excel but not the workbook didn't work, but eliminating the With,
End With and qualifying each command directly did solve the problem.

In addition the displayalerts = false now works also which eliminated the
prompt to save the file over an existing file.

This must be one of those Excel undocumented features!!

Thanks Guys!!

Dim appExcel As Object
Dim WBook As Object

'OPEN EXCEL

Set appExcel = CreateObject("Excel.Application")
Set WBook = appExcel.Workbooks.Open(strTemplateDir & strWBName)

' Excel.Application.DisplayAlerts = False
appExcel.DisplayAlerts = False '<== changed
With appExcel
.Range("A11").Value = Nz(Trim(rst![CustomerName]), " ")
.Range("A12").Value = Nz(Trim(rst![Address]), " ")
.Range("A13").Value = Nz(Trim(rst![City]), " ") & ", " & _
Nz(Trim(rst![State]), " ") & " " & Nz(Trim(rst![Zip]), " ")
.ActiveWorkbook.SaveAs filename:=strDocDir & strFileName, _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
.Visible = True
End With

ErrorHandlerExit:
rst.Close
WBook.close SaveChanges:=False '<== added
appExcel.Quit ' <== added
Set rst = Nothing
Set WBook = Nothing
Set appExcel = Nothing

Exit Sub

Might help.

If that doesn't work, then the next step might be to do away with the with
statement and qualify each individual command

--
Regards,
Tom Ogilvy

I am running Excel from MS Access. Basically I populate some cells from
Access data and then save and activate the workbook. After closing excel an
Excel.exe process remains in task manager. How do I get this to end and
still show the workbook?

Code is:

Dim appExcel As Object
Dim WBook As Object

'OPEN EXCEL

Set appExcel = CreateObject("Excel.Application")
Set WBook = appExcel.Workbooks.Open(strTemplateDir & strWBName)

Excel.Application.DisplayAlerts = False
With appExcel
.Range("A11").Value = Nz(Trim(rst![CustomerName]), " ")
.Range("A12").Value = Nz(Trim(rst![Address]), " ")
.Range("A13").Value = Nz(Trim(rst![City]), " ") & ", " & _
Nz(Trim(rst![State]), " ") & " " & Nz(Trim(rst![Zip]), " ")
.ActiveWorkbook.SaveAs filename:=strDocDir & strFileName, _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
.Visible = True
End With

ErrorHandlerExit:
rst.Close
Set rst = Nothing
Set WBook = Nothing
Set appExcel = Nothing

Exit Sub