Get Filename with VBA GetOpenFilename

Introduction to VBA GetOpenFilename

VBA GetOpenFilename allows you to select a file from a file explorer window without actually opening the file. The method returns a boolean of “False” if no file is selected or the dialog is canceled, and returns either a string or an array of strings if file(s) is/are selected.

It’s worth noting that, rather than a boolean, GetOpenFilename will return a string equal to “False” if your data type is a String instead of a Variant. This will make more sense when you look at these examples.

You might be wondering why you would want to get a file path but not open the file. Let’s say you wanted to grab tab-delimited source files that you’ll parse later and insert into a spreadsheet.

You may have a macro that takes a couple minutes to run, but the first minute is spent doing some preprocessing. You don’t want your user to wait a full minute before being prompted to select the files he wants to process.

In situations like this, it’s better to ask the user up front which file paths he wants to get, then run your macro and open the files when you actually need to access them. Sometimes it just doesn’t make sense from a system memory or a user experience perspective to open files immediately.

VBA GetOpenFilename Example

Basic Example

Sub basic_get_user_file()

Dim fileStringBasic As String

fileStringBasic = Application.GetOpenFilename()

If fileStringBasic <> "False" Then
    'your code for a single file here
End If

End Sub

Make powerful macros with our free VBA Developer Kit

Tutorials like this can be complicated. That’s why we created our free VBA Developer Kit and our Big Book of Excel VBA Macros to supplement this tutorial. Grab them below and you’ll be writing powerful macros in no time.

When you execute the above macro, the most basic file picker window will appear. The basic file picker window has no customization or restrictions on types of files to select.

VBA GetOpenFilename File Picker

Note that, by default, the VBA GetOpenFilename method will only allow the user to select a single file. The return value is a string, so whatever you do with the rest of your code, it should be manipulating a string. In this case, the name of your file is stored in the string fileStringBasic.

The GetOpenFilename file picker is just one of several ways to work with files in VBA. 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.

Restricting File Types with VBA GetOpenFilename

If you simply need a quick and dirty solution, the above will work fine. But let’s say you want to enforce some file type restrictions. For example, what if you had a huge list of .txt and .csv files, but you only want to view the .txt files? To do this, you can use the FileFilter argument in the GetOpenFilename method.

You should list your restrictions as a displayed name and a file extension pair, separated by a comma. Separate each subsequent allowable file type with another comma. In this example, we allow regular text files and some arbitrary file type we call “special” files (with the extension .special). The FileFilter argument is the first argument, so we decided to not use named arguments here. You certainly can if you want to, though.

Sub get_user_file()

Dim fileString As String

fileString = Application.GetOpenFilename("Text Files (.txt), *.txt, Special Files, *.special", 2)

If fileString <> "False" Then
    'your code for a single file here
End If

End Sub

VBA GetOpenFilename Prompt with specific filetypes

The “2” in our code marks the default file type, based on an array version of the FileFilter string. The index starts at 1, not zero, so if we used “1” in the so-called FilterIndex argument, we would default to first item in the dropdown: text files.

This GetOpenFilename example also returns a string of the selected filepath or the string “False” if the dialog is closed without selecting a file.

Selecting Multiple Files with VBA GetOpenFilename

...and changing the file picker title

If you want to allow the user to select multiple files, we need to set the MultiSelect argument to TRUE. It’s essential to be aware that with MultiSelect enabled, the function returns an array, not a string. If you forget, you will get type mismatch errors when you run your code. Notice, we also changed the file picker dialog title to “Select Multiple Files” so the user knows s/he can select more than one file.

Sub get_multiple_user_files()

Dim fileArray As Variant    'must be variant or you will get type errors

fileArray = Application.GetOpenFilename(Title:="Select Multiple Files", MultiSelect:=True)

If VarType(fileArray) >= vbArray Then
    'your code for one or more files here
End If

End Sub

Running this code will produce the following file picker dialog box:

This VBA GetOpenFilename Prompt allows multiple files to be selected

When you declare your variables with the intention of using MultiSelect, make sure to use Variant data types. Even if the user selects a single file, an array will be returned with one entry. If you declared your variable as a string, you will get a type mismatch error.

Unfortunately, if the file picker window is canceled, the function returns the Boolean FALSE. A variant (if a file is selected) and a Boolean (if the window is canceled) are two different variable types, so you could find yourself with a type mismatch errors if you’re not careful. To avoid this, it’s best to check that the variable type you’re working with is vbArray.

How can you do that? You use the VarType function, which tells you the type of variable (integer, string, array) you input. The value you get from the VarType function is an integer that corresponds to the variable type (7 is a date, 8 is a string, etc.). The Microsoft VarType description might be helpful if you’re interested in other return values.

For a basic array, also called a vbArray, the number is 8192, Another constant is often added to this base number depending on the type of array. Thus you should use VarType(fileArray) >= 8192 (greater than or equal to), since all VBA arrays will either yield 8192 or a larger number.

For you folks using the GetOpenFilename method on a Windows machine, that completes the tutorial. If you are targeting Mac users, however, you get the option to change the word that’s printed on the Open button. You can change the text of the button to something else using the ButtonText argument, similar to how you can change it for msoFileDialogFolderPicker. Surprisingly enough, changing the ButtonText argument on a Windows machine does absolutely nothing.

If you haven’t already done so, subscribe to my free wellsrPRO VBA Training Program using the form below.