Excel VBA allows you to open a workbook directly – all you need is the full path of the file, including the file name. However, locating and supplying the file path each time may can be tedious when working with multiple files. In this guide, we’re going to show you how to display File Open dialog in VBA.

Download Workbook

Opening a workbook in VBA

You can open workbooks in VBA using the Workbooks.Open method. This method accepts fifteen optional arguments, including the file name of the workbook you want to open.

Workbooks.Open (FileName, UpdateLinks, ReadOnly, Format, Password, WriteResPassword, IgnoreReadOnlyRecommended, Origin, Delimiter, Editable, Notify, Converter, AddToMru, Local, CorruptLoad)

Supply the file name with its full path to open the workbook.

Workbooks.Open “C:\My Documents\June\Income.xlsx”

Or

Workbooks.Open Filename:=”C:\My Documents\June\Income.xlsx”

Either line of code can open the workbook in the given path. The opened workbook becomes the active workbook.

Check out other optional arguments to determine how you want to open your workbook, such as in read-only mode, by updating external links, or with a password. Here is the documentation.

Displaying File Open dialog

File Open dialog can return the file name, which is needed for Workbooks.Open, along with its path. To display the File Open dialog, you need to call the Application.GetOpenFilename method.

Application.GetOpenFilename (FileFilter, FilterIndex, Title, ButtonText, MultiSelect)

The Application.GetOpenFilename method can take five optional arguments using which you can select the accepted file types, title of the dialog, or allow selecting multiple files.

To open the dialog in default state (without any filtering and ability to select single file) use it without any arguments. Assign the command to a string variable to set the variable with selected file’s full path.

Dim FullFileName as String
FullFileName = Application.GetOpenFilename

File Filter

On the other hand, applying a filter can be helpful in giving the end user only the files they need. You can use FileFilter to set filters on the Open File dialog box.

The FileFilter argument accepts a special string specifying file filtering criteria. You need to supply file types as friendly name – file type pairs. Each pair, name and type is separated by a comma (,) character.

friendly name 1, file type 1, friendly name 2, file type 2,

If a friendly name covers multiple types, use semicolon characters to split file types:

friendly name 1, file type 1; file type 2; file type …, friendly name 2, file type 4; file type 5; file type …,

Here is an example for displaying the dialog that accepts Excel Files only.

FullFileName = Application.GetOpenFilename(FileFilter:=”Excel Files,*.xl*”)

The following sample demonstrates the scenario at the above screenshot which displays two items as filters:

ExternalFileName = Application.GetOpenFilename(FileFilter:=”Excel Files,*.xl*;*.xm*,Text Files,*.txt;*.csv”)

Multiple Files

Another important feature of the Open File dialog is its ability to allow selecting multiple files. If the MultiSelect argument is set to True, the Open File dialog returns each selected file name in an array. Thus, you need to assign the dialog to a Variant type of variable instead of String.

Dim ExternalFileName As Variant
ExternalFileName = Application.GetOpenFilename(FileFilter:=”Excel Files,*.xl*,Text Files,*.txt;*.csv”, MultiSelect:=True)

When you click the Open button, the assigned variable will have an array of filenames with paths. A common scenario is to use a loop to access each file name.

For i = LBound(ExternalFileName) To UBound(ExternalFileName)
Workbooks.Open ExternalFileName(i)
Next i

You can check out All You Need to Know on How to Create a VBA loop in Excel article to learn more about loops.

Before start, you need to open the VBA (Visual Basic for Applications) window and add a module. A module is where you can write code.

  1. Press Alt + F11 to open the VBA window
  2. In the VBA window, click Insert on the toolbar
  3. Click the Module option

If you are unfamiliar with macros and VBA, just check How to create a macro in Excel guide for a jump start.

Open a single workbook

The following code displays the Open File dialog for Excel and some text files by allowing to select a single file only. After file name and path are set to the string variable, the code can copy content from the opened file to the existing file. You can simply copy this code and paste your VBA.

Sub SingleFile()
‘Turn off screen updates to hide window transactions
Application.ScreenUpdating = False
‘Ignore alerts such as “large amount of data” message while copying
Application.DisplayAlerts = False

‘Define and set variables
Dim PrimaryFileName As String, ExternalFileName As String
PrimaryFileName = ThisWorkbook.Name

‘Call Open File dialog
ExternalFileName = Application.GetOpenFilename(FileFilter:=”Excel Files,*.xl*,Text Files,*.txt;*.csv”)
‘End the macro if no ile is selected
If ExternalFileName = False Then
MsgBox “You are not selected a file”
Exit Sub
End If

‘Open the specified file and execute your code
Workbooks.Open ExternalFileName
Range(“A1:G20”).Copy ‘Copy range from external file
Windows(PrimaryFileName).Activate ‘Activate the primary file
Sheets.Add After:=ActiveSheet ‘Add a new sheet
ActiveSheet.Paste ‘Paste into new sheet
Workbooks(GetFilenameFromPath(ExternalFileName)).Close SaveChanges:=False ‘Close the external file without saving

‘Reactivate alerts and screen updates
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

How to display File Open dialog in VBA 03 - Single File

Note: GetFilenameFromPath is a custom function which parse the file name from the full path. You can find its code in the example file.

Open multiple workbooks

This time Application.GetOpenFilename method is updated for multiple file selection. You can see the MultiSelect:=True argument in the code below. This is an example where the code was modified to execute same action in a loop.

Sub MultipleFile()
‘Turn off screen updates to hide window transactions
Application.ScreenUpdating = False
‘Ignore alerts such as “large amount of data” message while copying
Application.DisplayAlerts = False

‘Define and set variables
Dim PrimaryFileName As String, ExternalFileName As Variant, i As Integer
PrimaryFileName = ThisWorkbook.Name

‘Call Open File dialog
ExternalFileName = Application.GetOpenFilename(FileFilter:=”Excel Files,*.xl*,Text Files,*.txt;*.csv”, MultiSelect:=True)
‘End the macro if no ile is selected
If Not IsArray(ExternalFileName) Then
MsgBox “You are not selected a file”
Exit Sub
End If

‘Open the each file and execute your code
For i = LBound(ExternalFileName) To UBound(ExternalFileName)
Workbooks.Open ExternalFileName(i)
Range(“A1:G20”).Copy ‘Copy range from external file
Windows(PrimaryFileName).Activate ‘Activate the primary file
Sheets.Add After:=ActiveSheet ‘Add a new sheet
ActiveSheet.Paste ‘Paste into new sheet
Workbooks(GetFilenameFromPath(ExternalFileName(i))).Close SaveChanges:=False ‘Close the external file without saving
Next i

‘Reactivate alerts and screen updates
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

How to display File Open dialog in VBA 04 - Multiple Files