How to open and close a workbook in VBA

Opening a workbook in VBA

To open a workbook we will use the VBA Open workbook method.

The basic VBA code to open an excel file is:

Workbooks.Open (“File_Name”)

You can assign this to a workbook object such as:

You need to make sure you include the full path and the name of the workbook to be able to open a workbook.

This method takes a few Optional parameters, please see a few examples demonstrating the most commonly used ones:

Parameter UpdateLinks

While opening a workbook that has links to other excel files manually, you will be prompted with a question to Update Links or not. With VBA we can do this using the UpdateLinks parameter in the VBA Open workbook method. It takes the following values:

0-The links will not be updated

3- The links will be updated

See the example below:

Parameter ReadOnly

Using this parameter, we can tell excel what to do if the file that is trying to open is already open in another computer. If we set the parameter to True, the VBA will open this workbook in a Read Only mode:

Parameter Password

We will use this parameter when the workbook that you are attempting to open is password protected:

5.png

If you would like to read more about this topic and the rest of the parameters that this method accepts, check out the Microsoft guide to using it here:

Workbooks.Open method (Excel) | Microsoft Docs

Closing a Workbook in excel VBA

To close a file in excel VBA we will use the Close method.

Basic syntax:

Workbooks (“File_Name”).Close

Continuing our example above:

If the workbook that you are trying to close have unsaved changes, you will be prompted with a dialogue box asking if you want to save the changes made to this workbook.

To avoid this, we will add an argument to the Close method and specify in our code if we want the changes to be saved or not:

In the example above, we are telling excel not to save the changes made to this workbook. You can set this to true to save the changes.

If you would like to read more about this topic, check out the Microsoft guide to using it here:

Workbook.Close method (Excel) | Microsoft Docs