To CLOSE an Excel file, you need to use the “Close” method. With this method you can, specify if you want to save the changes or not. And, if you want to save and close a file that is not saved yet you can specify the path where you want to save it before closing.
Following is the syntax for the CLOSE method.
Workbook.Close (SaveChanges, FileName, RouteWorkbook)
Steps to Close a Workbook
- Specify the workbook that you want to close.
- Use the close method with that workbook.
- In the code method, specify if you want to save the file or not.
- In the end, mention the location path where you want to save the file before closing.
In this tutorial, we will look at different ways that you can use to close a workbook in Excel using VBA. But before that, make you have the developer tab on the ribbon and open the visual basic editor to run these codes.
Close a Workbook without Saving
If you want to save the active workbook without saving you need to use a code like the following.
In this code, I have specified the “False” for the “SaveChanges” argument. So VBA will ignore if there’s any changes in the workbook which are not saved.
And if you want to close a specific workbook you can use the name of that workbook. Just like the following code.
If you have data in the workbook and you skip the “SaveChanges” argument, then Excel will show a dialog box to confirm if you want to save the workbook or not.
The point is: It is better to specify the “SaveChanges” argument even if it’s optional.
Close a Workbook after Saving
As you have seen, there’s an argument in CLOSE method to specify the path location. Let’s say if you wish to save the “Book6” to the folder on the desktop. Here’s the code that you need to use.
Workbooks("Book6").Close _ SaveChanges:=True, _ Filename:="C:\Users\Dell\Desktop\myFolder\myFile.xlsx"
This code is going to save the workbook “Book6” into the folder that is saved on my desktop with the name “myFIle.xlsx”. But here’s one thing that you need to take care of: IF you already have a workbook with the same name then it will replace that file with the new one.
Don’t worry, there’s a solution that you can use. The following code checks if there’s any file exists with the name that you want to use
Sub vba_close_workbook() Dim wbCheck As String wbCheck = Dir("C:\Users\Dell\Desktop\myFolder\myFile.xlsx") If wbCheck = "" Then Workbooks("Book6").Close _ SaveChanges:=True, _ Filename:="C:\Users\Dell\Desktop\myFolder\myFile.xlsx" Else MsgBox "Error! Name already used." End If End Sub