How to close an Excel workbook using VBA and macros, including how to save the file before you close it or discard any changes.
Selecting Which Workbook to Close
Close Workbook While Saving Changes
Close Workbook Without Saving Changes
Let the User Decide to Save Changes or Not
First, we need to tell the macro to choose the current workbook to close or another workbook to close.
We use this piece of code to close the current or currently active workbook and close that.
ActiveWorkbook.Close
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.
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
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
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
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.