VBA Close Workbook (Excel File)

You need to use the “Close” method to close an Excel file. With this method, you can specify whether you want to save the changes or not. If you want to save and close a file that has not been 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

1. Specify the workbook that you want to close.
2. Use the close method with that workbook.
3. In the code method, specify whether you want to save the file.
4. In the end, mention the location path where you want to save the file before closing.

In this tutorial, we will examine different ways 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 “False” for the “SaveChanges” argument. So, VBA will ignore any changes in the workbook that are not saved.

If you want to close a specific workbook, you can use its name, just like the following code.

Workbooks("book1").Close SaveChanges:=False

If you have data in the workbook and you skip the “SaveChanges” argument, Excel will show a dialog box to confirm whether you want to save the workbook.

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 that specifies the path location. Let’s say you wish to save “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 will save the workbook “Book6” into the folder 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, 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