How to display File Open dialog in VBA

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.

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

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.

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

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
   

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.

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 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