VBA Open / Close Workbook
In this Article
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 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:
Workbooks.Open "C:\VBA Folder\Sample file 1.xlsx"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:
ActiveWorkbook.SaveWhen you reference a sheet or range and omit the workbook name, VBA will assume you are referring to the ActiveWorkbook:
Sheets("Sheet1").Name = "Input"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.
Sub OpenWorkbookToVariable()
Dim wb As Workbook
Set wb = Workbooks.Open("C:\VBA Folder\Sample file 1.xlsx")
wb.Save
End Sub
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:
Sub OpenWorkbook ()
Dim strFile As String
strFile = Application.GetOpenFilename()
Workbooks.Open (strFile)
End Sub
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:
Workbooks.AddOpen New Workbook To Variable
This procedure will open a new workbook, assigning it to variable wb:
Sub OpenNewWorkbook()
Dim wb As Workbook
Set wb = Workbooks.Add
End SubOpen 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.
Workbooks.Open "C:\VBA Folder\Sample file 1.xlsx", , TrueOpen Password Protected Workbook
A workbook might be password-protected. Use this code to open the password-protected workbook:
Workbooks.Open "C:\VBA Folder\Sample file 1.xlsx", , , "password"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:
Sub OpenWB()
Dim wb As Workbook
Set wb = Workbooks.Open("C:\VBA Folder\Sample file 1.xlsx", True, True)
End SubClose 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:
Workbooks.Close ("C:\VBA Folder\Sample file 1.xlsx")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:
ActiveWorkbook.CloseClose All Open Workbooks
To close all open Workbooks, you can simply use this code:
Workbooks.CloseClose First Opened Workbook
This will close the first opened/created workbook:
Workbooks(1).CloseReplace 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:
ActiveWorkbook.Close savechanges:=FalseSave and Close Without Prompt
Similarly this will save and close a Workbook without showing the save prompt:
ActiveWorkbook.Close savechanges:=TrueNote: 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:
Sub OpenMultipleNewWorkbooks()
Dim arrWb(3) As Workbook
Dim i As Integer
For i = 1 To 3
Set arrWb(i) = Workbooks.Add
Next i
End SubOpen All Excel Workbooks in a Folder
This procedure will open all Excel Workbooks in a folder, using the Open File Dialog picker.
Sub OpenMultipleWorkbooksInFolder()
Dim wb As Workbook
Dim dlgFD As FileDialog
Dim strFolder As String
Dim strFileName As String
Set dlgFD = Application.FileDialog(msoFileDialogFolderPicker)
If dlgFD.Show = -1 Then
strFolder = dlgFD.SelectedItems(1) & Application.PathSeparator
strFileName = Dir(strFolder & "*.xls*")
Do While strFileName <> ""
Set wb = Workbooks.Open(strFolder & strFileName)
strFileName = Dir
Loop
End If
End Sub
Check if a Workbook is Open
This procedure will test if a workbook is open:
Sub TestByWorkbookName()
Dim wb As Workbook
For Each wb In Workbooks
If wb.Name = "New Microsoft Excel Worksheet.xls" Then
MsgBox "Found it"
Exit Sub 'call code here, we'll just exit for now
End If
Next
End SubWorkbook_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.
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.
Sub OpenTextFile()
Dim strFile As String
Dim strBody As String
Dim intFile As Integer
strFile = "C:\data\test.txt"
intFile = FreeFile
Open strFile For Input As intFile
strBody = Input(LOF(intFile), intFile)
'loop here through your text body and extract what you need
''some vba code here
Debug.Print strBody
Close intFile
End SubThe 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.
Sub AppendToTextFile()
Dim strFile As String
Dim strBody As String
Dim intFile As Integer
strFile = "C:\data\test.txt"
intFile = FreeFile
Open strFile For Append As intFile
'add two lines to the bottom
Print #intFile, "This is an extra line of text at the bottom"
Print #intFile, "and this is another one"
'close the file
Close intFile
End SubThe 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.
Sub OpenWordFile()
Dim wApp As Object
Dim wDoc As Object
Set wApp = CreateObject("Word.Application")
Set wd = wApp.documents.Open("c:data\test.docx")
wApp.Visible = True
End SubThis code will open a copy of Word, and then open the document test.docx.