Subscribe for Weekly Tutorials
BONUS: subscribe now to download our Top Tutorials Ebook!
Close Excel Workbook using VBA Macros
How to close an Excel workbook using VBA and macros, including how to save the file before you close it or discard any changes.
Sections:
Selecting Which Workbook to Close
Close Workbook While Saving Changes
Close Workbook Without Saving Changes
Let the User Decide to Save Changes or Not
Selecting Which Workbook to Close
First, we need to tell the macro to choose the current workbook to close or another workbook to close.
Current Workbook
We use this piece of code to close the current or currently active workbook and close that.
ActiveWorkbook.Close
Other Workbook
We use this piece of code to close any specific open workbook.
Workbooks("test.xlsx").Close
Replace test.xlsx with the name of the file that you want to close.
Close Workbook While Saving Changes
To have Excel automatically save any changes for the workbook that you want to close, just put True behind the close workbook code from above like this:
ActiveWorkbook.Close True
or, to close a specific file like this:
Workbooks("test.xlsx").Close True
Close Workbook Without Saving Changes
To have an Excel window close WITHOUT saving any changes, just put False behind the close workbook code from above like this:
ActiveWorkbook.Close False
or, to close a specific file like this:
Workbooks("test.xlsx").Close False
Let the User Decide to Save Changes or Not
You use the basic code from the first section and don't include a True or False after it and a dialog box will open asking if you want to save the file or not; it looks like this:
Close the currently active or visible workbook:
ActiveWorkbook.Close
Close a specific workbook:
Workbooks("test.xlsx").Close
Notes
You may run into issues with messages popping up depending on your implementation of this code and your setup and it can help to turn off ScreenUpdating for Excel. Make sure to turn it back on when you are finished though.
If Application.DisplayAlerts is set to False before you close the workbook, you won't see a popup asking if you want to save it or not before closing it. If this is the case, you may lose data if you wanted to save the file before closing it, so test your code on a sample workbook first.
Download the sample files for this tutorial to test everything out.
Question? Ask it in our Excel Forum
Tutorial: Simple way to open an Excel workbook using VBA and macros. Syntax Workbooks.Open ("File...
Tutorial: Macro to get data from a workbook, closed or open, over a network or locally on your comp...
Tutorial: How to prompt a user for their input in Excel. There is a simple way to do this using VBA ...
Tutorial: How to use VBA/Macros to iterate through each cell in a range, either a row, a column, or ...
Tutorial: In order to combine a cell that has a date with a cell that has a time, using a Macro and ...
Tutorial: How to prevent a user from closing an Excel workbook. The user won't know what to do! Th...