Introduction | Comparison | Basic Setup | Advanced file selection | Applications |
VBA msoFileDialogFilePicker Introduction
Use msoFileDialogOpen to browse for and select files with VBA. msoFileDialogFilePicker is part of the Application.FileDialog family in Excel. The msoFileDialogFilePicker is just one of many Application.FileDialog MsoFileDialogType boxes.
If you haven’t already done so, check out our tutorial on the msoFileDialogFolderPicker which shows you how to select a folder in VBA.
In this tutorial, we’ll start by showing you the basic setup for selecting a file with the msoFileDialogFilePicker and VBA. In and of itself, this is extremely easy, but for your final product you typically want to customize the dialog box in several ways. If you want to become a true file I/O expert, you should look at our comprehensive VBA File I/O Cheat Sheet filled with 50+ tips for working with files and over 30 file input/output macro examples.
The primary goals for customizing the dialog box are to improve the user experience and prevent user errors. Thus, in the second part of this tutorial we’ll show you some of the most important ways to achieve these goals and we’ll then finish off with a brief discussion on the potential applications of the msoFileDialogOpen function.
msoFileDialogFilePicker vs GetOpenFilename
The msoFileDialogFilePicker behaves much like the VBA GetOpenFilename method we talked about last month. As a matter of fact, if you know how to use GetOpenFilename, you’ll instinctively know how to use its cousin, msoFileDialogFilePicker. The msoFileDialogFilePicker VBA File Dialog box is a bit more flexible, but might be a hair slower than the GetOpenFilename approach. The speed is up for debate…
Just like the GetOpenFilename method, the msoFileDialogFilePicker doesn’t actually open your file. It simply stores the selected files so you can open them later in your macro.
Note, the GetOpenFileName method only works with Excel. The Application.FileDialog property, on the other hand, works with most Microsoft Office products after 2010, but it won’t work with all Microsoft products. For example, trying to display a FileDialog window in Outlook will generate run-time error 438. To get the Folder Picker to work in Outlook, you must make reference to an instance of Excel or Word from within your Outlook VBA.
Basic Setup - VBA msoFileDialogFilePicker
Insert the code below into a standard code module:
Sub GetFilePathBasic()
' (1) Shows the msoFileDialogFilePicker dialog box.
' (2) Checks if the user picked a file.
' (3) Stores the path to the selected file in a string type variable.
Dim strFilePath As String
With Application.FileDialog(msoFileDialogFilePicker)
' show the file picker dialog box
If .Show <> 0 Then
strFilePath = .SelectedItems(1)
' *********************
' put your code in here
' *********************
' Example: print the path of the selected file to the immediate window
Debug.Print strFilePath ' remove in production
End If
End With
End SubWhen you run the code above, you’ll see a prompt like this:
VBA msoFileDialogFilePicker Prompt
The .Show command displays the file picker dialog box. When the dialog box closes, .Show can have 2 possible values: a -1 or a 0.
- -1 - A file is selected and the OK button is pressed.
- 0 - The Cancel button or the X is clicked.
After you select a file and click OK, the
If your only goal is to let the user select a single file and retrieve the path to it, then this basic code will get the job done! However, in real-life situations you typically want to make customizations and augmentations to the code to improve the user experience and prevent obvious user errors. The three most common ways to achieve these goals are:
- Changing the title of the dialog box
- Filtering the files the user can select from by file type
- Allowing the user to select more than one file at once
In the next section we’ll present a more sophisticated procedure which contains all the above-mentioned features and more!
Advanced file selection with VBA msoFileDialogFilePicker
Insert both procedures below into a standard code module:
Sub GetFilePathArray()
' (1) Calls the GetFilePath function.
' (2) Prints the returned array of file paths to the immediate window in the Visual Basic Editor.
Dim i As Long
Dim varFilePaths As Variant
' call file picker function
varFilePaths = GetFilePath(Array("xlsm", "xlsx", "xls"), True, "C:\test\testfile.txt")
' print the returned file paths to the immediate window
If IsArray(varFilePaths) Then
For i = LBound(varFilePaths) To UBound(varFilePaths)
Debug.Print i & ": " & varFilePaths(i)
Next i
End If
End Sub
Function GetFilePath(Optional fileType As Variant, Optional multiSelect As Boolean, Optional defaultPath) As Variant
' (1) Shows the msoFileDialogFilePicker dialog box.
' (2) Checks if the file type parameter was passed and whether the passed parameter is an array.
' (3) Sets the dialog box title, file filter and default file according to the parameters passed.
' (4) Returns the paths to the selected files in an array, or displays an error message.
Dim blArray As Boolean
Dim i As Long
Dim strErrMsg As String, strTitle As String
Dim varItem As Variant
'check whether the file type parameter was passed
If Not IsMissing(fileType) Then
'check whether the passed fileType variable is an array
blArray = IsArray(fileType)
' error
If Not blArray Then strErrMsg = "Please pass an array in the first parameter of this function!"
End If
'proceed
If strErrMsg = vbNullString Then
' set title of dialog box
If multiSelect Then strTitle = "Choose one or more files" Else strTitle = "Choose file"
' set dialog properties
With Application.FileDialog(msoFileDialogFilePicker)
.AllowMultiSelect = multiSelect
.InitialFileName = defaultPath
.Filters.Clear
If blArray Then .Filters.Add "File type", "*." & Join(fileType, ", *.")
.Title = strTitle
' show the file picker dialog box
If .Show <> 0 Then
ReDim arrResults(1 To .SelectedItems.Count) As Variant
' return multiple file paths
If blArray Then
For Each varItem In .SelectedItems
i = i + 1
arrResults(i) = varItem
Next varItem
' return single file path
Else
arrResults(1) = .SelectedItems(1)
End If
' return results
GetFilePath = arrResults
End If
End With
' error message
Else
MsgBox strErrMsg, vbCritical, "Error!"
End If
End FunctionThe GetFilePathArray Function
We’ve rewritten the GetFilePathBasic() procedure to the GetFilePath function. This function takes three optional parameters and returns the path of one or more selected files in an array. We demonstrated how to invoke the function using our GetFilePathArray() procedure. Let’s explain this procedure before we explain the GetFilePath function itself.
You’ll notice the following line of code in the GetFilePathArray() procedure:
varFilePaths = GetFilePath(Array("xlsm", "xlsx", "xls"), True, "C:\test\testfile.txt")This line of code tells Excel to return the results from the GetFilePath function in the Variant type variable, GetFilePath function will be stored in an array. The For-Next loop at the end of the GetFilePathArray procedure just prints the contents of this array to the immediate window (the debugger window).
When you execute the GetFilePathArray procedure above, you’ll get a file picker dialog box, like this:
VBA msoFileDialogFilePicker Customized Prompt
We’ve put in red frames where this dialog box differs from the basic file picker dialog box and we’ll explain how these changes were implemented in the next section!
The GetFilePath function
The first optional parameter of the GetFilePath function is an array of the file types you want the user to select from. Although these are file extension names, do not put a dot in front of them. The function will do this automatically for you. You can alter the function if you like to check for the dot, but I didn’t do it.
The second optional parameter is a Boolean type variable which specifies whether you want to allow the user to select more than one file at a time. The default value is
The third optional parameter is a String type variable which specifies the path to the default file path of the file picker dialog box. By setting it to a valid file path, the file picker dialog box will initialize in that location with the file name inserted in the input field below. If the folder path is valid, but the file name is not, the file name will still be inserted into the input field at the bottom of the file picker dialog box. The user will not be able to select this file though, as it doesn’t exist! Attempting to select an invalid file will prompt an error message, like this:
VBA msoFileDialogFilePicker Error Message
Changing the title
You set the title of the file picker dialog box with the
Filtering the files the user can select from by file type
Here, we first check whether the GetFilePath function was passed. If yes, we check whether this parameter is an array or not with the
Allowing the user to select more than one file at once
We use the second parameter of the GetFilePath function,
Application Ideas - VBA msoFileDialogFilePicker
Displaying a file selection dialog box is particularly useful when you want to
- Process multiple user-specific files in one batch. For instance, suppose your goal is to build a distributable Excel application which processes information from several user-specified files, and then inserts the processed information into a report. Thanks to the
GetFilePathfunction a lot of your work is already done! - Introduce restrictions to prevent user errors. The file filters feature of the VBA msoFileDialogFilePicker is really powerful now that you know how to use it to restrict the user’s options. It’s a very common mistake to confuse different file types with one another, but by restricting the allowed file types the user can select from, you can effectively minimize the probability of user errors.
If you haven’t already done so, please subscribe to my free wellsrPRO VBA Training Program using the form below. In addition to getting tutorials like this in your email, you’ll get a whole series of VBA lessons reserved exclusively for my VBA subscribers.
