Closing Excel When Using CreateObject to Open It

[Steve]

I have code in an Access module that opens an Excel file using CreateObject.
I have a lot of code in the Excel file and the user's only interaction in
Excel is via five buttons I offer, one of which is a "Save & Close" button.
Everything works fine EXCEPT Excel does not close when "Save & Close" is
clicked. The workbook closes, but Excel does not. Below is my code in the
Excel module that I use to save the workbook and close it, then quit Excel,
but the "Application.Quit" statement is skipped (I used a break to watch what
happens). It all closes fine when I open the file on my own (double clicking
the xls file), but when I open it via the Access function I created, only the
workbook closes, not the Excel application. I do not have a "Quit" statement
in the Access function I created because the user needs to interact and use
Excel, so I can't use Access to close Excel right after it opens it. I guess
it's not a crime if Excel is left running after the user modifies the
workbook, but I would like to close Excel completely if possible.

Sub CloseWorkBook()
On Error Resume Next
Application.Caption = "Microsoft Excel"
Application.WindowState = xlMaximized
RestoreToolbars
AppActivate "Microsoft Access"
Sheets("Sheet1").Select
ThisWorkbook.Close (True)
Application.Quit
End Sub

 

[Tim Zych]

Excel is not closing because the workbook with the code is closed before
Quit can run.

ThisWorkbook.Close (True)
Application.Quit

Here's another way to do it. This assumes the workbook has been opened from
a specified location, so the Save method will save changes in the same
location.

Sub SaveAndClose()
' .. your stuff ..
ThisWorkbook.Save ' Simulates your use of ThisWorkbook.Close (True)
Application.DisplayAlerts = False
Application.Quit
End Sub

 

[Steve]

I changed it to .save and it fixed it. Thanks.

Excel is not closing because the workbook with the code is closed before
Quit can run.


Here's another way to do it. This assumes the workbook has been opened from
a specified location, so the Save method will save changes in the same
location.

Sub SaveAndClose()
' .. your stuff ..
ThisWorkbook.Save ' Simulates your use of ThisWorkbook.Close (True)
Application.DisplayAlerts = False
Application.Quit
End Sub

--
Tim Zych
http://www.higherdata.com
Compare data in Excel and find differences with Workbook Compare
Free and Pro versions available