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:
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:
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:
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:
We will use this parameter when the workbook that you are attempting to open is password protected:
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:
Closing a Workbook in excel VBA
To close a file in excel VBA we will use the Close method.
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:
Free Excel Shortcuts Cheat Sheet
Subscribe to download the file, and to get our latest Excel tips and discounts by email.