Open Folder and Select File Using VBA

Sometimes, your VBA code may require the user to provide a filename and its full path before the code performs its actual task.

However, prompting the user to enter the file’s full name manually is not always the best approach.

Most users find it challenging to recall filenames, file extensions, and paths accurately. In such cases, it is advisable to have your subroutine open a folder, allow the user to browse to the desired folder, and select the requested file or files instead.

In this tutorial, I will show you how to get a file’s full name from the user using the ‘Application.GetOpenFileName’ method and ‘Application.FileDialog’ property.

Table of Contents

Using the Application.GetOpenFileName Method

The ‘Application.GetOpenFileName’ method displays the standard ‘Open’ dialog box and retrieves the name/s of the file/s the user has selected without opening the file/s.

The method’s syntax:

expression.GetOpenFilename ([FileFilter], [FilterIndex], [Title], [ButtonText], [MultiSelect])

expression is a variable representing the Excel application.

The ‘Application.GetOpenFileName’ method takes the five optional Variant parameters below.

Parameter	Description
FileFilter	This determines the file types in the ‘Open’ dialog box (for example, .XLSX). It has pairs of file filter strings that are followed by the wildcard file filter specification, with commas delimiting each part and pair. You can specify multiple filters for the user to select from. If omitted, it defaults to the All Files (.), .* filter.
FilterIndex	Specifies which file filters the dialog box displays by default. The first file filter is used if this argument is left out or greater than the number of available filters.
Title	Shows the caption for the title bar of the dialog box. The default caption is ‘Open.’
ButtonText	Only applies to the Macintosh version of Excel.
MultiSelect	If ‘True,’ the user can select multiple files. If ‘False,’ the user can select only one file. ‘False’ is the default value.

Using Application.GetOpenFileName To Open Folder and Select File

The example code below opens a dialog box allowing the user to select a file for import. The procedure defines three file filters.

Sub GetFileNameImport()
Dim FileInfo As String
Dim FilterIndex As Long
Dim Title As String
Dim FileName As Variant

FileInfo = "Comma Separated Files (*.csv),*.csv," & _
    "Excel Files (*.xls*),*.xls*," & _
    "All Files (*.*),*.*"
FilterIndex = 2
Title = "Choose a File to Import"
FileName = Application.GetOpenFilename(FileInfo, _
  FilterIndex, Title)
If FileName = False Then
  MsgBox "You did not select a file."
Else
  MsgBox "You selected " & FileName
End If
End Sub

When you execute the subroutine, it displays the dialog box below:

Open file dialog box that shows all the files in the folder

Note: The dialog box’s appearance will vary depending on your display settings and your version of Windows.

If you select a file from the ones displayed and click ‘Open,’ a message box displays the file’s full path, as in the example below:

Browse dialog box that shows all the files in the folder

To allow the user to select multiple files, you can modify the code as below:

Sub GetFileNamesImport()
Dim FilesInfo As String
Dim FilterIndex As Long
Dim Title As String
Dim FileNames As Variant
Dim i As Integer
FilesInfo = "Comma Separated Files (*.csv),*.csv," & _
    "Excel Files (*.xls*),*.xls*," & _
    "All Files (*.*),*.*"
FilterIndex = 2
Multiselect = True
Title = "Choose Files to Import"
FileNames = Application.GetOpenFilename(FilesInfo, FilterIndex, Title, MultiSelect:=True)
If Not IsArray(FileNames) Then
    MsgBox "You did not select files."
Else
For i = LBound(FileNames) To UBound(FileNames)
    Debug.Print FileNames(i)
Next i
End If
End Sub

This modified code stores the full names of the files the user selects in an Array variable, FileNames, and then prints them to the Immediate window.

Explanation of the Code

The code declares two String variables, one Long variable, and one Variant variable.

The FileName variable is declared a Variant to accommodate a Boolean value of ‘False’ if the user clicks ‘Cancel’ or a String value of a filename if the user selects a file.

The FileInfo variable is assigned a string with information about the types of files the user can select.

The FilterIndex variable is set to 2, indicating that the default file filter is for Excel files. The Title variable is set to ‘Choose a File to Import.’

If the user cancels the dialog box displayed by the code, a message box informs the user that they did not select a file.

If the user browses to the desired folder and selects a file, a message box displays the file’s full path.

Note that the ‘Application.GetOpenFileName’ only returns the full path of the selected file and does not open the file.

If you want the subroutine to open the selected file, add the statement ‘Workbooks.Open Filename’ after the ‘Else’ keyword.

Also read: How to Open Excel Files Using VBA

Using the Application.FileDialog Property

The ‘Application.FileDialog’ property returns a ‘FileDialog’ object that represents an instance of the file dialog.

The property’s syntax:

expression.FileDialog (fileDialogType)

expression is a variable representing the Excel application.

The property’s ‘fileDialogType’ argument is required and is of the ‘MsoFileDialogType’ data type, which can be one of the constants below.

Name	Value	Description
msoFileDialogOpen	1	Displays the ‘Open’ dialog box, allowing the user to open a file.
msoFileDialogSaveAs	2	Displays the ‘Save As’ dialog box, allowing the user to save a file.
msoFileDialogFilePicker	3	Displays the ‘File Picker’ dialog box, allowing the user to select a file.
msoFileDialogFolderPicker	4	Displays the ‘Folder Picker’ dialog box, allowing the user to select a folder.

Using Application.FileDialog Property To Open Folder and Select File

The example VBA code below opens the ‘File Open’ dialog box, allowing the user to select one or more files:

Sub UseBrowseDialog()
Dim lngCount As Long
With Application.FileDialog(msoFileDialogFilePicker)
.AllowMultiSelect = True
.Show
For lngCount = 1 To .SelectedItems.Count
    Debug.Print .SelectedItems(lngCount)
Next lngCount
End With
End Sub

Note: Before executing the code, check whether the Immediate window is open. If it is not open, press CTRL + G in the VB Editor to display it.

When you execute the code, it displays the ‘Browse’ dialog box below:

If you browse to the desired folder, select multiple files, and click ‘Open,’ the full paths of the files are printed to the Immediate window, as in the example below:

Immediate window showing the list of all the selected files

The code prompts the user to select one or more files using the Browse dialog box. Then, it prints the full path of each selected file to the Immediate window in the VB Editor.

In this tutorial, I showed you how to use the ‘Application.GetOpenFilename’ method and ‘Application.FileDialog’ property to open a folder using Excel VBA and let the user select a file or files. I hope you found the tutorial helpful.

Other Excel articles you may also like: