How to close Excel using VBA

The need to close Excel after running a macro arises all the time especially in large-scale Excel reporting automations where you may be collating data from multiple Excel files.  To learn how to close Excel from within VBA then read on…

 

Excel has various options within VBA that can be used to close Excel down.  It is important to know exactly what you want to achieve as they all have slightly different results.

For example closing a workbook generally means that the workbook is closed but the Excel application remains open, closing Excel the application will close Excel down and by default that means the workbook will be closed with it.

Sometimes you may need to close down a certain workbook so again that is something to consider in your code.  For example maybe you have a data file that you copy data from and then you just want to close this down and leave the main report open.

Below are the most common scripts for achieving all of these options:

Close everything down, all workbooks and Excel

Application.Quit

This shuts the lot down, all open workbooks and all open Excel programs.   You will need to have saved any changes that you want before this code is completed, Excel will prompt you to save or not unless you have switched alerts off.

Close a certain workbook down

Workbooks(“Name of Workbook”).Close

This will close down a particular workbook.  You do not need to specify the file path as this will generate an error known as run-time error ‘9’, subscript out of range.  Excel will know which of the open workbooks you mean just by the workbook name.

If you have alerts turned off then Excel will just close the file without saving changes but if you want to be more professional with your VBA then add the save settings to the end of the code like so:

Workbooks(“Name of Workbook”).Close SaveChanges:=True

Or

Workbooks(“Name of Workbook”).Close SaveChanges:=False

Close all workbooks but leave Excel open

Workbooks.Close

This will close all the workbooks you have open.  As with the above you will be prompted to save changes, if any have occurred, unless you either have alerts switched off or you have previously saved the file.

Summary

To recap the first thing to do is decide on what you are trying to achieve with closing Excel, do you want everything closed down, just the workbook closed down or just a specific workbook closed down and Excel to remain open.

Always consider your saving options.

Sometimes you may not want to save so you can get around the Excel prompt message asking you to save the workbook by switching alerts off, a more professional approach will set the save criteria in the VBA script and this also helps prevent unwanted results that can occur when you do switch alerts off.

 

 

Keep Excelling,

Leave a Comment