VBA Open File Dialog Box

VBA allows you to choose a file to open using the Application.GetOpenFilename method. In this tutorial, you will learn how to open a file dialog and set parameters.

If you want to learn how to open and close a file, you can click on this link: VBA Open/Close File

Open a File Dialog in VBA

If you want to open a file in VBA, first you need to open a file dialog for choosing a file. Here is the code:

Dim strFile As String

strFile = Application.GetOpenFilename(FileFilter:="Excel files (*.xlsx*), *.xlsx*", Title:="Choose an Excel file to open", MultiSelect:=True)

As you can see, the method has several parameters. FileFilter allows you to filter file types which you need, in our case .xlsx files.

In the Title parameter, you can set a title of the dialog box. If you want to allow opening multiple files, you need to set MultipleSelect to True. If you don’t set this parameter, only one file can be selected.

Image 1. Open a file dialog

 

As you can see in Image 1, the dialog box appears with the title Choose an Excel file. Only Excel files are filtered and we can select multiple files.

 

Open a File Dialog Box in a Specific Folder

If you want to open a dialog box in a specific folder, you have to use the method .FileDialog with the parameter msoFileDialogFilePicker. In the example, we will open a dialog box in the folder C:\VBA Folder. Here is the code:

Dim fd As Office.FileDialog
Dim strFile As String

Set fd = Application.FileDialog(msoFileDialogFilePicker)

With fd

    .Filters.Clear
    .Filters.Add "Excel Files", "*.xlsx?", 1
    .Title = "Choose an Excel file"
    .AllowMultiSelect = False

    .InitialFileName = "C:\VBA Folder"

    If .Show = True Then

        strFile = .SelectedItems(1)

    End If

End With

First you need to declare the variable fd type Office.FileDialog and the string variable for file taking:

Dim fd As Office.FileDialog
Dim strFile As String

After this, you need to set fd to Application.FileDialog(msoFileDialogFilePicker):

Set fd = Application.FileDialog(msoFileDialogFilePicker)

Now, within With fd End With, we can set multiple parameters:

.Filters.Clear

.Filters.Add "Excel Files", "*.xlsx?", 1

.Title = "Choose an Excel file"

.AllowMultiSelect = False

Here we clear the file filters (.Filters.Clear) and set it to .xlsx (.Filters.Add “Excel Files”, “*.xlsx?”, 1).

Also, we can set the title of the dialog box: .Title = “Choose an Excel file”.

We can limit a user to select only one file by:

.AllowMultiSelect = False

To open a dialog box in the folder we want, we need to put this line of the code:

.InitialFileName = "C:\VBA Folder"

At the end we will just open the dialog box with all the parameters previously set:

If .Show = True Then

    strFile = .SelectedItems(1)

End If

 

When we execute this code, the dialog box for file opening appears in the folder C:\VBA Folder:

Image 2. Open a file dialog in a specific folder