In this Article Show
In this tutorial, you will learn how to use VBA to open and close Excel Workbooks and other types of Files in several ways. 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 VBAOpen Workbook From PathIf 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 – ActiveWorkbookWhen 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 VariableYou 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 DialogYou 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 WorkbookThis line of code will open a new workbook:
VBA Coding Made EasyStop 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! Learn More!! Open New Workbook To VariableThis procedure will open a new workbook, assigning it to variable wb:
Open Workbook SyntaxWhen 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-OnlyWhen workbook is opened read-only, you can’t save over the original file. This prevents the file from being edited by the user.
VBA Programming | Code Generator does work for you! Open Password Protected WorkbookA workbook might be password-protected. Use this code to open the password-protected workbook:
Open Workbook Syntax NotesNotice 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 VBAClose Specific WorkbookSimilarly 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 WorkbookIf you want to close the Workbook which is currently active, this line of code will enable you to do that:
AutoMacro | Ultimate VBA Add-in | Click for Free Trial! Close All Open WorkbooksTo close all open Workbooks, you can simply use this code:
Close First Opened WorkbookThis will close the first opened/created workbook:
Replace 1 with 2 to close the second opened / created workbook and so on. Close Without SavingThis will close a Workbook without saving and without showing the save prompt:
Save and Close Without PromptSimilarly 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. AutoMacro | Ultimate VBA Add-in | Click for Free Trial! Other Workbook Open ExamplesOpen Multiple New WorkbooksThis procedure will open multiple new workbooks, assigning the new workbooks to an array:
Open All Excel Workbooks in a FolderThis procedure will open all Excel Workbooks in a folder, using the Open File Dialog picker.
Check if a Workbook is OpenThis procedure will test if a workbook is open:
AutoMacro | Ultimate VBA Add-in | Click for Free Trial! Workbook_Open EventVBA 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 VBAYou 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 ContentsThe 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 itWe 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 itWe 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. Can you use a macro to open a file?As a programmer, you're likely to come across many situations where other files need to be opened for reading, writing, or appending data. A macro can help you easily handle these files, and the Open method is your ticket.
How do I open an Excel file in Excel VBA?In Excel VBA, you can use Workbooks. Open method to open an Excel file, where you need to specify the file path of the Excel workbook that you want to open. While the Workbooks. Open method can use 15 arguments, in most cases, we only need to use two or three of them.
How do I open a CSV file in a macro?Macro to parse a csv or txt file
You can copy the code and insert it into a VBA module. Just highlight it with the mouse, press CTRL+C and insert with CTRL+V. If you are viewing this page on a small screen, some of the code lines may appear "broken," but they will be okay when you paste into a VBA module.
How do I open a file in Excel?How to Open Workbooks in Excel. Click the File tab.. Click Open. Press Ctrl + O to quickly display the Open tab of the Backstage view.. Select the location where the file is saved. You can choose from: Recent: Recent files you've worked on. ... . Select the file you want to open.. Click Open.. |