VBA Open / Close Workbook
VBA allows you to open or close files using the standard methods .Open and .Close.
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 full path name in the function. Here is the code:
This line of the code opens “Sample file 1” file from the “VBA Folder”.
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 an object 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:
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:
VBA Coding Made Easy
Stop searching for VBA code online. Learn more about AutoMacro — A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users!
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:
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
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.
Open Other Types of Files in VBA
You can use the VBA to open other types of files with VBA – such as txt or Word files.
Open a Text file and Read its Contents
The VBA open method allows you to read or write to the file once you have opened it. To read the contents of a file, we can open the file for INPUT.
The code above will open the text file “test.txt” and then it will read the entire contents of the file to the strBody variable. Once you have extracted the file data into the strBody variable, you can use it for what you require. Using the Debug.Print command above enables us to see the contents of the strBody variable in the Immediate window in the VBE.
Open a Text File and Append to it
We can also open a text file in VBA, and then append to the bottom of the file using the Append method.
The above code will open the text file and then append 2 lines of text to the bottom of the file using the #intFile variable (the # sign is the key!). The code then closes the file.
Opening a Word File and Writing to it
We can also use VBA in Excel to open a Word file.
This code will open a copy of Word, and then open the document test.docx.
VBA Code Examples Add-in
Easily access all of the code examples found on our site.
Simply navigate to the menu, click, and the code will be inserted directly into your module. .xlam add-in.
VBA Open Workbook (Excel File)
To open a workbook using VBA, you need to use the “Workbook.Open” method and specify the path of the file (make sure to specify the full path to the workbook with name and extension file type). This method has a total of fifteen optional arguments which you can use to deal with different kinds of files.
In this tutorial, we will explore it in detail and look at an alternative method that you can use.
Steps to Open a Workbook using VBA
- To start the code, use the “Workbooks” object.
- Type a dot (.) after that and select the Open method from the list.
- Specify the file path in the first argument and make sure to enclose it in double quotation marks.
- In the end, run the code to open the workbook.
Now it’s time to look at the syntax of the method that you just have used in the above example. As I mentioned, there are fifteen arguments that you can use:
You won’t be using all these arguments. But a few of them are quite important and could be useful for you in the real world.
Opening a Password Protected Workbook
If you want to open a workbook that is password-protected, in that case, you can specify the password with the password argument.
Here I have a workbook on the desktop that has the password “test123” and now I want to open it and unprotect it at the same time. Following is the code that I need to use.
Opening a Workbook as Read Only
When you open a workbook as read-only you can’t make changes to the same workbook, but you need to save a copy of it.
Как с помощью VBA открыть файл Excel
В статье «Как с помощью VBA скопировать данные из одной книги в другую» был рассмотрен код открытия одной книги Excel из другой. Пердположем, что по ходу выполнения макроса нам приходится открывать/закрывать книгу неоднократно, либо наш файл находится где-то глубоко на сетевом диске, либо мы обращаемся к группе файлов находящихся в одной директории. В этих и ряде других случаев нам может оказаться полезной операция ввода переменной, которая будет хранить весь адрес целиком либо какую-то часть адреса. Назовем эту переменную «adr», далее мы можем присвоить переменной путь в самом коде, а можем присвоить значение ячейки листа, в которую пропишем этот путь (так мы можем упростить работу с файлом пользователю, который не хочет лезть в Макрос), тогда наш код будет выглядеть так:
Open Excel file for reading with VBA without display
I want to search through existing Excel files with a macro, but I don’t want to display those files when they’re opened by the code. Is there a way to have them open «in the background», so to speak?
10 Answers 10
Not sure if you can open them invisibly in the current excel instance
You can open a new instance of excel though, hide it and then open the workbooks
As others have posted, make sure you clean up after you are finished with any opened workbooks
If that suits your needs, I would simply use
with the added benefit of accelerating your code, instead of slowing it down by using a second instance of Excel.
To open a workbook as hidden in the existing instance of Excel, use following:
Using ADO (AnonJr already explained) and utilizing SQL is possibly the best option for fetching data from a closed workbook without opening that in conventional way. Please watch this VIDEO.
OTHERWISE, possibly GetObject(<filename with path>) is the most CONCISE way. Worksheets remain invisible, however will appear in project explorer window in VBE just like any other workbook opened in conventional ways.
If you want to read a particular sheet, need not even define a Workbook variable
A much simpler approach that doesn’t involve manipulating active windows:
From what I can tell the Windows index on the workbook should always be 1 . If anyone knows of any race conditions that would make this untrue please let me know.
Even though you’ve got your answer, for those that find this question, it is also possible to open an Excel spreadsheet as a JET data store. Borrowing the connection string from a project I’ve used it on, it will look kinda like this:
Note that «RegistrationList» is the name of the tab in the workbook. There are a few tutorials floating around on the web with the particulars of what you can and can’t do accessing a sheet this way.
Just thought I’d add. 🙂
The problem with both iDevlop’s and Ashok’s answers is that the fundamental problem is an Excel design flaw (apparently) in which the Open method fails to respect the Application.ScreenUpdating setting of False. Consequently, setting it to False is of no benefit to this problem.
If Patrick McDonald’s solution is too burdensome due to the overhead of starting a second instance of Excel, then the best solution I’ve found is to minimize the time that the opened workbook is visible by re-activating the original window as quickly as possible:
Open the workbook as hidden and then set it as «saved» so that users are not prompted when they close out.
This is somewhat derivative of the answer posted by Ashok.
By doing it this way though you will not get prompted to save changes back to the Excel file your reading from. This is great if the Excel file your reading from is intended as a data source for validation. For example if the workbook contains product names and price data it can be hidden and you can show an Excel file that represents an invoice with drop downs for product that validates from that price list.
You can then store the price list on a shared location on a network somewhere and make it read-only.