Excel process doesn't close?

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
try adding

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

Set rst = Nothing
Set WBook = Nothing
Set appExcel = Nothing
 
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
 
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 said:
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 said:
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
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Excel automation goes wrong with every 'even' attempt 1
Instance of Excel running after closing Excel 3
RePost - Creating WBooks 4
Cursor change during code execution 2
Excel Automation and Vista 11
refresh excell 1
Terminating Excel 1
Save Excell file as 8

Back
Top