Let’s talk about closing workbooks using VBA today. You can easily close a workbook using the “Close” method in VBA.
In this article, I will explain how the close method works in VBA and then cover some examples showing you how to use it to close Excel workbooks.
Workbook.Close Method in VBA
The “Close” method of the workbook object allows you to close a workbook, potentially saving any changes and releasing the associated resources.
The syntax of the method is as follows:
expression.Close(SaveChanges, Filename, RouteWorkbook)
Note: expression is a variable representing a Workbook object.
The “Close” method has the following three optional parameters:
- SaveChanges: This Boolean parameter specifies whether you want to save changes made to the workbook before closing it. If you set the parameter to “True,” any unsaved changes you have made to the workbook will be saved. If you set it to “False,” any unsaved changes will be discarded. If you don’t include this parameter, Excel will prompt you to decide whether or not to save changes.
- Filename: This is a string parameter that specifies the name and path of the file where you want to save the workbook. If you want to save the workbook to a different file name or location, you can set the filename parameter here. If you omit this parameter, Excel will use the existing file name and location. This argument is used when saving a copy of the workbook with a different name.
- RouteWorkbook: This is a Boolean parameter used in older versions of Excel and is no longer in use. You can omit this parameter or set it to “False.”
In this article, I will show you how to close a workbook using VBA in ten different scenarios.
How to Close Workbook Using VBA
Let’s look at different examples where you can use the Workbook.Close Method in VBA.
Scenario #1: Close the Current Workbook and Save Changes
If you want to close the current workbook and save any changes made to it, you can use the following code:
Sub CloseSaveChangesCurrentWb()
' Close the workbook that contains this macro and save changes
ThisWorkbook.Close SaveChanges:=True
End SubYou can also use the following alternative code that uses the “ActiveWorkBook” object:
Sub CloseSaveChanges2()
' Close the active workbook and save any changes made
ActiveWorkbook.Close SaveChanges:=True
End SubAlso read: VBA to Unprotect a Workbook
Scenario #2: Close the Current Workbook and Discard Unsaved Changes
To close the workbook without saving any unsaved changes, use the following code:
Sub CloseDiscardChanges()
' Close the workbook and discard any changes made
ThisWorkbook.Close SaveChanges:=False
End SubWhen this code is run, it closes the current workbook (ThisWorkbook) and discards any unsaved changes by setting the SaveChanges parameter to False.
Note: You can also use an alternative code that uses the “ActiveWorkBook” object, as explained in Scenario #1
Scenario #3: Close a Specific Workbook by Name and Save Changes
If you want to close a workbook and save changes by specifying its name, for instance, “Sales,” you can use the following code:
Sub CloseSaveChangesByName()
' Close the workbook named "Sales.xlsx" and save any changes made
Workbooks("Sales.xlsx").Close SaveChanges:=True
End SubWhen executed, it closes the workbook specifically named “Sales.xlsx” and saves any changes made to it by setting the SaveChanges parameter to True.
Also read: VBA Check If Workbook Is Open
Scenario #4: Close a Specific Workbook by Name and Discard Unsaved Changes
If you want to close a workbook and discard any unsaved changes by specifying its name, for instance, “Sales,” you can use the following code:
Sub CloseDiscardChangesByName()
' Close the workbook named "Sales.xlsx" and discard any changes made
Workbooks("Sales.xlsx").Close SaveChanges:=False
End SubWhen this code is run, it closes the workbook specifically named “Sales.xlsx” and discards any unsaved changes by setting the SaveChanges parameter to False.
Also read: VBA Protect Workbook
Scenario #5: Close All Open Workbooks and Save Changes Except the Active Workbook
To close all open workbooks and save changes except the one running the VBA code, you can use the following code:
Sub CloseSaveChangesAllWbksExceptCurrent()
' Declare a Workbook object variable named wb
Dim wb As Workbook
' Loop through each workbook in the Workbooks collection
For Each wb In Workbooks
' Check if the name of the workbook is not equal to the name of the workbook containing this macro
If wb.Name <> ThisWorkbook.Name Then
' Close the workbook and save any changes made
wb.Close SaveChanges:=True
End If
' Move to the next workbook in the Workbooks collection
Next wb
End SubWhen this code is run, it iterates through all open workbooks and closes them, saving any changes.
The only workbook that remains open is the one containing this VBA code, identified as ThisWorkbook. It uses a For Each loop to go through the Workbooks collection and an If statement to skip over ThisWorkbook.
Note: If you want to close all the open workbooks, except the current one, and discard any unsaved changes, set the “SaveChanges” parameter to “False.”
Scenario #6: Close Workbook by Index and Save Changes
You can also close a workbook by its index in the collection of open workbooks. The index starts from 1 for the first open workbook.
For instance, to close the second open workbook by index and save changes, use the code below:
Sub CloseSaveChangesbyIndex()
' Close the workbook at index 2 in the Workbooks collection and save any changes made
Workbooks(2).Close SaveChanges:=True
End SubWhen this code is executed, it targets the workbook at the 2nd index position within the Workbooks collection and closes it.
The code also ensures that any changes made to this workbook are saved by setting the SaveChanges parameter to True. Note that the index is 1-based, meaning the first workbook in the collection is at index 1, the second at index 2, and so on.
Note: If you want to close a workbook by index and discard any unsaved changes, set the “SaveChanges” parameter to “False.”
Also read: VBA Run Macro from Another Workbook
Scenario #7: Close Workbook by Variable and Save Changes
If you have a reference to a workbook in a variable, you can close it using that variable.
The following code sets a workbook called “Sales” to the “wb” object variable and uses the variable to close the workbook, saving changes:
Sub CloseSaveChangesWbByVariable()
Dim wb As Workbook
' Set the wb variable to the workbook named "Sales.xlsx"
Set wb = Workbooks("Sales.xlsx")
' Close the workbook stored in the wb variable and save any changes made
wb.Close SaveChanges:=True
End SubThis VBA code closes a specific workbook named “Sales.xlsx” and saves any changes.
It does so by declaring a Workbook object variable named wb and then setting it to the workbook named “Sales.xlsx”.
Once the variable is set, the code closes the workbook and saves any changes by setting the SaveChanges parameter to True.
Note: If you want to close a workbook by variable and discard any unsaved changes, set the “SaveChanges” parameter to “False.”
Scenario #8: Close All Workbooks and Save Changes
To close all open workbooks and save changes, you can use the following code:
Sub CloseSaveChangesAllWbs()
Dim wb As Workbook
' Loop through each workbook in the Workbooks collection
For Each wb In Workbooks
' Close the workbook and save any changes made
wb.Close SaveChanges:=True
' Move to the next workbook in the Workbooks collection
Next wb
End SubWhen this VBA code is executed, it loops through all the open workbooks and closes them, saving any changes made to each one.
It employs a For Each loop to iterate through the Workbooks collection and closes each workbook by setting the SaveChanges parameter to True.
This will save any modifications before closing.
Note that this will also close the workbook containing this macro, so make sure to save the code before running it.
Scenario #9: Close Active Workbook and Save a Copy to a Different File Name in Same Folder
If you want to close the active workbook, save changes, and save a copy of the file to a different file name in the same folder, use the following code and replace the file name with your desired name:
Sub CloseActiveWbSaveChangesFileName()
' Close the active workbook, save any changes made, and provide a Filename "Application Guide"
ActiveWorkbook.Close SaveChanges:=True, Filename:="Application Guide"
End SubWhen this VBA code is executed, it closes the currently active workbook and saves any changes. The SaveChanges parameter is set to True, which means any unsaved changes will be saved.
Additionally, the Filename parameter is specified as “Application Guide”.
However, it’s important to note that this Filename argument usually has no effect unless the workbook is saved as a copy or sent as an email attachment. So, in most cases, the workbook will be saved with its current name and location.
Note: If you want to close the active workbook, save changes, save a copy of the file to a different file name in the same folder, and discard all unsaved changes, set the “SaveChanges” parameter to “True.”
Also read: Delete Files in a Folder Using VBA in Excel
Scenario #10: Close Active Workbook and Save a Copy to a Different File Name in a Different Folder
If you want to close the active workbook, save changes, and save a copy of the file to a different file name in a different folder, use the following code and replace the file path with your desired path:
Sub CloseActiveWbSaveChangesFileNamePath()
' Close the active workbook, save any changes, and attempt to specify a Filename path "C:\Excel Tutorials\Application Guide"
ActiveWorkbook.Close SaveChanges:=True, Filename:="C:\Excel Tutorials\Application Guide"
End SubWhen this VBA code is executed, it closes the currently active workbook and attempts to save any changes to a specified file path “C:\Excel Tutorials\Application Guide”.
The SaveChanges parameter is set to True, ensuring that any changes are saved.
However, it’s crucial to note that the Filename argument typically doesn’t change the save location when using the Close method. It’s generally used when the workbook is being saved as a copy or being sent as an email attachment.
In most scenarios, the workbook will still be saved in its original location with its current name.
Note: You can set the “SaveChanges” parameter to “False” if you do not want to save changes.
In this tutorial, you learned how to close a workbook using VBA in ten different scenarios.
These include closing the active workbook and saving any changes made, closing the current workbook and saving a copy with a different file name, and closing all workbooks while saving any changes made.
The tutorial provided clear instructions on implementing these scenarios using VBA so that you can efficiently manage your workbooks in Excel.
We hope you found the tutorial helpful.
Other Excel articles you may also like:
