Skip to content

VBA Open / Close Workbook

Associated Files Download Links

VBA allows you to open or close files using the standard methods .Open and .Close. In this tutorial, you will learn how to open and close a Workbook in several ways.

If you want to learn how to check if a file exists before attempting to open the file, you can click on this link: VBA File Exists

Open a Workbook in VBA

Open Workbook From Path

If you know which file you want to open, you can specify its name in the function. Here is the code:

This line of the code opens “Sample file 1” file from the “VBA Folder”. You must specify the full path of the file.

Open Workbook – ActiveWorkbook

When you open a workbook, it automatically becomes the ActiveWorkbook.  You can reference the newly opened workbook like so:

When you reference a sheet or range and omit the workbook name, VBA will assume you are referring to the ActiveWorkbook:

Open Workbook and Assign to a Variable

You can also open a workbook and assign it directly to a variable. This procedure will open a workbook to the wb variable and then save the workbook.

Assigning workbooks to variables when they open is the best way to keep track of your workbooks

Workbook Open File Dialog

You can also trigger the workbook Open File Dialog box. This allows the user to navigate to a file and open it:

vba open close file

Image 1. Open the file

 

As you can see in Image 1, with this approach users can choose which file to open. The Open File Dialog Box can be heavily customized. You can default to a certain folder, choose which types of files are visible (ex. .xlsx only), and more. Read our tutorial on the Open File Dialog Box for detailed examples.

Open New Workbook

This line of code will open a new workbook:

Open New Workbook To Variable

This procedure will open a new workbook, assigning it to variable wb:

Open Workbook Syntax

When you use Workbooks.Open you might notice that there are many options available when opening the workbook:

vba open workbook syntax

The Filename is required. All other arguments are optional – and you probably won’t need to know most of the other arguments.  Here are the two most common:

Open Workbook Read-Only

When workbook is opened read-only, you can’t save over the original file. This prevents the file from being edited by the user.

Open Password Protected Workbook

A workbook might be password-protected. Use this code to open the password-protected workbook:

Open Workbook Syntax Notes

Notice that in the image above, we included a parenthesis “(” to show the syntax.  If you use parenthesis when working with Workbooks.Open, you must assign the workbook to a variable:

Close a Workbook in VBA

Close Specific Workbook

Similarly to opening a workbook, there are several ways to close a file. If you know which file you want to close, you can use the following code:

This line of code closes the file “Sample file 1” if it’s opened. If not, it will return an error, so you should take care of error handling.

Close Active Workbook

If you want to close the Workbook which is currently active, this line of code will enable you to do that:

Close All Open Workbooks

To close all open Workbooks, you can simply use this code:

Close First Opened Workbook

This will close the first opened/created workbook:

Replace 1 with 2 to close the second opened / created workbook and so on.

Close Without Saving

This will close a Workbook without saving and without showing the save prompt:

Save and Close Without Prompt

Similarly this will save and close a Workbook without showing the save prompt:

Note: There are several other ways to indicate whether to save or not save a Workbook and also whether to show prompts or not. This is discussed in more detail here.

Other Workbook Open Examples

Open Multiple New Workbooks

This procedure will open multiple new workbooks, assigning the new workbooks to an array:

Open All Excel Workbooks in a Folder

This procedure will open all Excel Workbooks in a folder, using the Open File Dialog picker.

Check if a Workbook is Open

This procedure will test if a workbook is open:

Workbook_Open Event

VBA Events are “triggers” that tell VBA to run certain code. You can set up workbook events for open, close, before save, after save and more.

Read our Workbook_Open Event tutorial to learn more about automatically running macros when a workbook is opened.

You may also like some of this related content...

VBA allows you to check if a file or folder exists by using the Dir
VBA allows you to rename an existing file, using the Name command. In this tutorial,
VBA allows you to delete an existing file, using the Kill command. In this tutorial,
VBA allows you to choose a file to open using the Application.GetOpenFilename method. In this
VBA allows you to copy a file, using the FileSystemObject. In this tutorial, you will
Advertisements
Automate Excel
Left Menu Icon