What is the Application.FileDialog VBA function and how do you use it? Basically, it returns a FileDialog object instance of the dialog. This is similar to the functionality of the standard Open and Save dialog boxes found in Microsoft Office applications. Using these dialog boxes, users can easily specify the files and folders.

What is the syntax?

expression .FileDialog (fileDialogType). It is a variable that represents an Application object. The FileDialog property is stored in each individual Office application’s Application object. It takes a single argument called DialogType that determines the type of FileDialog object that the property will return.

What is the required parameter?

Below are the data types and its definition

There are four types of FileDialog object such as follows:

 msoFileDialogFilePicker which allows the user to select one or more files. The file path directory that user can select are handled in the FileDialogSelectedItems colllection.

msoFileDialogFolderPicker which allows the user to select a path directory that are handled in the FileDialogSelectedItems collection.

msoFileDialogOpen which allows the user to select one or more files that you can open in the host application using the Execute method.

msoFileDialogSaveAs which allows the user to select a single file that you can save the current file as using the Execute method.

How to use msoFileDialogFilePicker?

Sample code and step by step procedure description:

1. Declare the subroutine name as “MyFilePicker()”
2. Declare the counter for the selected items as “lngCtr”
3. Use “With” , “End With” statement that repeatedly refer to a single object so that the statements can use a syntax when accessing members of the object.
4. Specify the value of “.AllowMultiSelect” property to “True” which means the user can select multiple files
5. Use the “.Show” method to display the File Picker
6. Use “. Next” statement to repeat a group of statement with the specified number of times
7. Use “MsgBox” to display the contents of the .SelectedItems path directory

Sub MyFilePicker()
Dim lngCtr As Long
' Open the file dialog
With Application.FileDialog(msoFileDialogFilePicker)
.AllowMultiSelect = True
.Show
' Display paths of each file selected
For lngCtr = 1 To .SelectedItems.Count
MsgBox .SelectedItems(lngCtr)
Next lngCtr
End With
End Sub

Executing this VBA code will display the File dialog box to pick which file to be selected:

File path directory will be displayed

How to use msoFileDialogFolderPicker?
Sample code and step by step procedure description:

Declare the subroutine name as “MyFolderPicker()”
Declare the counter for the selected items as “lngCtr”
Use “With” , “End With” statement that repeatedly refer to a single object so that the statements can use a syntax when accessing members of the object.
Specify the value of “.AllowMultiSelect” property to “True” which means the user can select multiple files
Use the “.Show” method to display the Folder Picker
Use “. Next” statement to repeat a group of statement with the specified number of times
Use “MsgBox” to display the contents of the .SelectedItems path directory

Sub MyFolderPicker()
Dim lngCtr As Long
' Open the file dialog
With Application.FileDialog(msoFileDialogFolderPicker)
.AllowMultiSelect = True
.Show
' Display paths of each folder selected
For lngCtr = 1 To .SelectedItems.Count
MsgBox .SelectedItems(lngCtr)
Next lngCtr
End With
End Sub
Executing this VBA code will display the File dialog box to pick which folder to be selected:

Folder path directory will be displayed

You can find further examples from SSO here.
How to use msoFileDialogOpen?
Sample code and step by step procedure description:

Declare the subroutine name as “MyFolderDialogOpen()”
Ceclare the counter for the selected items as “lngCtr”
Use “With” , “End With” statement that repeatedly refer to a single object so that the statements can use a syntax when accessing members of the object.
Specify the value of “.AllowMultiSelect” property to “True” which means the user can select multiple files
Use the “.Show” method to display the Folder Picker
Use “. Next” statement to repeat a group of statement with the specified number of times
Use “MsgBox” to display the contents of the .SelectedItems path directory

Sub MyFolderDialogOpen()
Dim lngCtr As Long
' Open the file dialog
With Application.FileDialog(msoFileDialogOpen)
.AllowMultiSelect = True
.Show
' Display paths of each folder selected
For lngCtr = 1 To .SelectedItems.Count
MsgBox .SelectedItems(lngCtr)
Next lngCtr
End With
End Sub
Executing this VBA code will display the File dialog box to pick which file to be launch:

How to use msoFileDialogSaveAs?
Sample code and step by step procedure description:

Declare the subroutine name as “MyFileSaveAs()”
Use “With” , “End With” statement that repeatedly refer to a single object so that the statements can use a syntax when accessing members of the object.
Use the “.Show” method to display the File Save As
Use “MsgBox” to display the contents of the .SelectedItems path directory

Sub MyFileSaveAs()
' Open the file dialog
With Application.FileDialog(msoFileDialogSaveAs)
.Show
End With
End Sub
Executing this VBA code will display the File dialog box to pick which file to be saved and enter the preferred filename

Below are the sample of common properties that you could use:

AllowMultiSelect – This property will determine if the user is allowed to select multiple files from a file dialog box.
DialogType – This property will return the msoFileDialogType constant representing the type of file dialog box.
ButtonName – This property will return a String representing the text that is displayed non the action item.
FilterIndex – This property will return an Integer indicating the default file filter.
InitialFileName – This property returns a String representing the path and/ or file name that is initially displayed in a file dialog box.
Title – This property returns the title of a file dialog box displayed using the FileDialog object.
InitialView – This property sets a MsoFileDialogView constant representing the initial presentation of files and folders in a file dialog box.

These are some of the useful links related to FileDialog functionality that you can use as a reference for different ways of implementation.

http://stackoverflow.com/questions/25153342/open-a-workbook-using-filedialog-and-manipulate-it-in-excel-vba
http://www.minnesotaithub.com/2013/08/select-a-file-with-file-dialog-in-ms-access-with-vba/
https://www.experts-exchange.com/questions/28313803/Tweaking-the-‘Open’-Dialog-Box-using-VBA.html
https://msdn.microsoft.com/en-us/library/microsoft.office.core.filedialog.allowmultiselect.aspx