VBA Close Workbook (Excel File)

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 Close 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 (only needed for a workbook that has never been saved).

In this tutorial, we will examine different ways to close a workbook in Excel using VBA.

Close a Workbook without Saving

If you want to close 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 the workbook has unsaved changes and you skip the “SaveChanges” argument, Excel will show a dialog box to confirm whether you want to save the workbook. If there are no changes, the workbook simply closes.

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” (a new workbook that has never been saved) to a 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 will save the workbook “Book6” into the folder on my desktop with the name “myFile.xlsx.” Change the path to a folder on your computer.

Note that Excel uses the file name only when the workbook doesn’t have a file name yet. If the workbook is already saved somewhere, SaveChanges:=True saves it to its current location, and the Filename argument is ignored. To save a copy with a new name, use SaveAs before you close the workbook.

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 a file already 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

The Dir function returns the file name if the file exists and an empty string if it doesn’t, so the workbook is saved and closed only when the name is free.

Close All Workbooks Except the Current One

To close every open workbook but keep the one that has the code, loop through the Workbooks collection and skip ThisWorkbook. The following code saves each workbook before closing it:

Sub close_all_other_workbooks()
    Dim wb As Workbook
    For Each wb In Workbooks
        If wb.Name <> ThisWorkbook.Name Then
            wb.Close SaveChanges:=True
        End If
    Next wb
End Sub

A new workbook that has never been saved will still ask you for a file name. And if you use Workbooks.Close instead, it closes all the open workbooks in one go, and Excel asks about saving each one that has changes.

Close the Workbook with the Code

You can also close the workbook where the code is written with ThisWorkbook.Close. Just keep in mind that the code stops running as soon as the workbook closes, so put this line at the end of the macro:

Sub close_this_workbook()
    ThisWorkbook.Save
    ThisWorkbook.Close SaveChanges:=False
End Sub

Frequently Asked Questions