Excel process doesn't close?
 

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

Excel process doesn't close?
 

try adding

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

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






"Al" wrote:

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

Excel process doesn't close?
 

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



"Al" wrote in message
...
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

Excel process doesn't close?
 

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

"Tom Ogilvy" wrote:

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



"Al" wrote in message
...
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