Closing Excel via VBA

Question

Long ago I had a macro that would open some workbooks, do some fiddling, then close - all fully automated, called from the commandline so I could batch file it and stick it in windows schedule to run when needed.

I've had call to do it again and of course no longer have access to those files (like four companies ago) so had a bash at recreating them, but stuck on closing excel.

ActiveWorkbook.Close SaveChanges:=True

This works to close the workbooks I'm updating, however, if I use it on the macro containing workbook, it closes the workbook within Excel, but leaves the instance of excel running (with no workbooks).

Application.Quit

This is what I think should be the right command, however this also throws an error:

Run-time error '1004':

Application-defined or object-defined error

However, it's odd. If I just run that command within excel on its own, there's no error, it only errors if the workbook calls the macro containing it.

As it's an auto-launching macro, I have an initial macro in ThisWorkbook that has the Private Sub Workbook_Open() which kicks everything off.

I'd read that Application.Quit can cause this error message when the workbook has an auto-launching macro and it's called from a module, rather than ThisWorkbook, so I moved the Sub QuitExcel() that I created, containing just Application.Quit but still get this error.

The suggested posts from SO when posting this gave a few things to try, like DisplayAlerts=False and saving the workbook, so I updated my quit sub to:

Sub QuitExcel()
    Application.DisplayAlerts = False
    Application.EnableEvents = False
    ThisWorkbook.Save
    Application.Quit
    ThisWorkbook.Saved = True
End Sub

(and various permutations of that)

However I still persist in getting the same error.

Interestingly, if I click "debug" (rather than end, continue is greyed out) Excel still quits, and doesn't actually go to debugging.

Anyone have experience in what I'm trying to achieve, I just want to kill Excel after it's finished running, from auto-launching a macro?

Answer 1

Answer turned out to be relatively easy, I need to close my workbook before quitting excel and the closing macro can't be in the ThisWorkbook, it has to be in a module (otherwise when the workbook is closed, access to the rest of the code is gone).

So my command ended up being:

Sub QuitExcel()
    ActiveWorkbook.Close SaveChanges:=False
    Application.Quit
End Sub

Just needed a bit more googling and trial/error.

Answer 2

You should know that VBA code only run in a workbook context ThisWorkBook , that mean when you close it using WorkBook.Close, there will be no code to execute. So, you only save then use Application.Quit Do not close the workbook For Each w In Application.Workbooks w.Save Next w Application.Quit