Introduction | Comparison | Basic Setup | Advanced file selection | Applications | Comments

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 Sub

When you run the code above, you’ll see a prompt like this:

VBA msoFileDialogFilePicker FileDialog
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 strFilePath variable will contain the path to the file. In this example, we print the file path to the immediate window in the Visual Basic Editor using Debug.Print. If the Cancel button or the X in the top right corner is clicked, the variable strFilePath will be blank.

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!


Write better macros in half the time
I see people struggling with Excel every day and I want to help. That's why I'm giving away my personal macro library for free. This powerful gift lets you automatically import all my macros directly into your spreadsheets with just one click.

I want to join the free wellsrPRO VBA Training program

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) 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
            .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 Function

Write better macros in half the time
I see people struggling with Excel every day and I want to help. That's why I'm giving away my personal macro library for free. This powerful gift lets you automatically import all my macros directly into your spreadsheets with just one click.

I want to join the free wellsrPRO VBA Training program

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

varItem = GetFilePath(Array("xlsm", "xlsx", "xls"), True)

This line of code tells Excel to return the results from the GetFilePath function in the Variant type variable, varItem. In case the user cancelled, the varItem variable will be empty, but if the user selected at least one file and clicked OK, the returned results from the 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 FileDialog with customizations
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 False, but in this example we’ve set this parameter to True so that the user can select multiple files at once.

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
VBA msoFileDialogFilePicker Error Message

Changing the title

You set the title of the file picker dialog box with the .Title property. In this case, we first check to see if the user can select more than file at once and then specify the title accordingly, but if you prefer you can just set the title directly to anything you want.

Filtering the files the user can select from by file type

Here, we first check whether the FileType parameter of the GetFilePath function was passed. If yes, we check whether this parameter is an array or not with the IsArray function. If the passed parameter is not an array, the function terminates with a message explaining why it terminated. Otherwise, we clear any existing file filters with the .Filters.Clear statement and then add the passed file types with the .Filters.Add statement. This is because we want our file filter to be mandatory so that the user must select files of the type we’ve specified. If you leave out the .Filters.Clear statement, your specified file filter will simply be appended to the list of any existing file filters!

Allowing the user to select more than one file at once

We use the second parameter of the GetFilePath function, multiSelect, to specify whether or not selecting more than one file is allowed and then we simply set the .AllowMultiSelect property to the value of this parameter. If you don’t pass this optional parameter, the value of multiSelect will be False, which means the user can only select a single file. False is the default in our custom GetFilePath function, just like it’s the default in the msoFileDialogFilePicker dialog box.


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 GetFilePath function 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.

Comments

If you haven’t already done so, please subscribe to my free wellsrPRO VBA Training Program. 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!

Share this article on Google+, Twitter and Facebook, then leave a comment below and let’s have a discussion.

This article was written by Michael H. Sorensen on behalf of The VBA Tutorials Blog.

About The VBA Tutorials Blog

Ryan Wells

The VBA Tutorials Blog was created by Ryan Wells, a Nuclear Engineer and professional VBA Developer. He created this site to help others learn to write macros in Excel. That's why he developed a unique 3-part free Excel training program to help others quickly learn VBA in a natural setting: right inside Excel.