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.
The 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.
Helpful Links: Run a Macro – Macro Recorder – Visual Basic Editor – Personal Macro Workbook
Close a Workbook without Saving
If you want to save the active workbook without saving you need to use code like the following.
ActiveWorkbook.Close SaveChanges:=False
In this code, I have specified the “False” for the “SaveChanges” argument. So VBA will ignore if there are 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.
Workbooks("book1").Close SaveChanges:=False
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 the 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:UsersDellDesktopmyFoldermyFile.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:UsersDellDesktopmyFoldermyFile.xlsx")
If wbCheck = "" Then
Workbooks("Book6").Close _
SaveChanges:=True, _
Filename:="C:UsersDellDesktopmyFoldermyFile.xlsx"
Else
MsgBox "Error! Name already used."
End If
End Sub
Related Tutorials
- Copy an Excel File (Workbook) using VBA
- VBA Activate Workbook (Excel File)
- VBA Combine Workbooks (Excel Files)
- VBA Create New Workbook (Excel File)
- VBA Delete Workbook (Excel File)
- VBA Open Workbook (Excel File)
- VBA Protect/Unprotect Workbook (Excel File)
- VBA Rename Workbook (Excel File)
- VBA Save Workbook (Excel File)