VBA FileDialog in Excel — FilePicker, FolderPicker, and Reading .SelectedItems

TL;DR — Application.FileDialog opens the standard Windows picker so the user chooses a file or folder instead of you hardcoding a path. Pick a type — msoFileDialogFilePicker, msoFileDialogFolderPicker, msoFileDialogSaveAs, or msoFileDialogOpen. Call .Show: it returns -1 if the user picked something and 0 if they cancelled — not the path. The path lives in .SelectedItems(1), a 1-based collection. FileDialog gives you a path; you still open or save it yourself.

Sub PickAFile()
    Dim fd As FileDialog
    Set fd = Application.FileDialog(msoFileDialogFilePicker)
    With fd
        .Title = "Choose a workbook"
        .AllowMultiSelect = False
        .Filters.Clear
        .Filters.Add "Excel Files", "*.xlsx;*.xlsm"
        If .Show <> -1 Then Exit Sub          ' -1 = picked, 0 = cancelled
        MsgBox "You picked: " & .SelectedItems(1)   ' 1-based collection
    End With
End Sub

Hardcoding "C:\Users\Me\Reports\Jan.xlsx" into a macro is the fastest way to make it break the moment someone else runs it. The professional habit is to ask. VBA gives you three tools for that, and the one idea behind all of them is worth getting straight first: these tools return a path string — they do not open, save, or touch a file. Getting the path and acting on the path are two separate steps. FileDialog is the most capable of the three because it is the only one that can also ask for a folder.

What you'll learn

The mental model that ties FileDialog, GetOpenFilename and GetSaveAsFilename together
The four dialog types and which job each one is for
Why .Show returns -1 and 0 instead of True/False, and how to test it
How to read the result from .SelectedItems, a 1-based collection
Picking a folder with FolderPicker — the thing the simpler functions cannot do
Allowing multi-select, adding filters, and the shared-object reset trap

The mental model: a configurable picker you read afterwards

Application.FileDialog is not a function that returns a path. It is an object you configure, show, and then read. The flow is always the same three beats:

Set fd = Application.FileDialog(msoFileDialogFilePicker)  ' 1. choose the TYPE
'    ... set .Title, .Filters, .AllowMultiSelect ...      ' 2. configure it
If fd.Show = -1 Then answer = fd.SelectedItems(1)         ' 3. show, then read the pick

.Show blocks until the user clicks a button and hands back a status, not the path. The path — or paths — land in .SelectedItems. Keep those two ideas apart and the whole object makes sense: showing the dialog and reading the choice are different lines. Everything else on this page is which type to pick and how to read the result.

The four dialog types

You choose the dialog's job by the constant you pass to Application.FileDialog(...):

Application.FileDialog(msoFileDialogFilePicker)    ' choose one or more existing files
Application.FileDialog(msoFileDialogFolderPicker)  ' choose a folder (no file)
Application.FileDialog(msoFileDialogOpen)          ' like FilePicker, plus an .Execute that opens
Application.FileDialog(msoFileDialogSaveAs)        ' choose a save path and name

For 90% of macros you want msoFileDialogFilePicker (get a file to read) or msoFileDialogFolderPicker (get a folder to loop over). The Open and SaveAs types carry an extra .Execute method that actually performs the open or save, but you rarely need it — reading the path and calling Workbooks.Open or SaveAs yourself is clearer and gives you control over what happens next.

Why .Show returns -1 and 0

This is the detail that trips people who expect .Show to return the chosen path. It returns an integer status:

If fd.Show = -1 Then
    ' user clicked OK / Open / Save - a selection exists
Else
    ' user clicked Cancel - .SelectedItems is empty, do NOT read it
End If

-1 means a pick was made; 0 means the user cancelled. It happens that -1 is what VBA calls True, so If fd.Show = -1 and If fd.Show both work — but write = -1, because it says what the number means. The important half is the Else: if the user cancelled, .SelectedItems is empty, and SelectedItems(1) raises a run-time error. Always guard the cancel before you read.

Reading the result: SelectedItems is 1-based

The user's choice is a collection, .SelectedItems, and like every VBA collection it counts from 1, not 0:

If fd.Show = -1 Then
    Dim i As Long
    For i = 1 To fd.SelectedItems.Count      ' collections are 1-based
        Debug.Print fd.SelectedItems(i)      ' each item is a full path string
    Next i
End If

For a single-file pick, .SelectedItems(1) is the whole story. The For i = 1 To .Count loop only matters once you turn on multi-select — and starting that loop at 0 is the classic off-by-one that raises "Subscript out of range." Each item is a complete path you can hand straight to Workbooks.Open, Dir, or a FileSystemObject.

Picking a folder — the thing only FileDialog can do

Here is the reason FileDialog earns its place next to the simpler functions: GetOpenFilename can only return a file. When you need a folder — to loop over every workbook inside it, say — FolderPicker is the tool:

Sub PickAFolder()
    Dim fd As FileDialog, folderPath As String
    Set fd = Application.FileDialog(msoFileDialogFolderPicker)
    If fd.Show <> -1 Then Exit Sub
    folderPath = fd.SelectedItems(1)
    If Right(folderPath, 1) <> "\" Then folderPath = folderPath & "\"  ' normalise
    MsgBox "Chosen folder: " & folderPath
End Sub

The folder comes back without a trailing backslash, so if you are about to append a filename (folderPath & "Report.xlsx") add the \ yourself — forgetting it is the number-one bug in folder-loop macros. From here, Dir(folderPath & "*.xlsx") walks every workbook in the folder.

Multi-select, filters, and the reset trap

Two settings cover most of the rest. AllowMultiSelect = True lets the user pick several files, which is exactly when the For i = 1 To .Count loop above pays off. Filters restrict what the dialog shows:

With Application.FileDialog(msoFileDialogFilePicker)
    .AllowMultiSelect = True
    .Filters.Clear                              ' RESET first - see below
    .Filters.Add "Spreadsheets", "*.xlsx;*.xlsm;*.xls"
    .Filters.Add "All Files", "*.*"
    If .Show = -1 Then MsgBox .SelectedItems.Count & " file(s) chosen"
End With

The trap worth naming: Application.FileDialog is a single shared object. Filters and settings from a previous call can still be sitting there. Always call .Filters.Clear before adding your own, and set .AllowMultiSelect explicitly every time, or a stray setting from earlier in the session leaks into your dialog. Treating the object as fresh when it is actually shared is a quiet, hard-to-reproduce bug.

FileDialog or GetOpenFilename? Pick by what you need back

If you need a folder, or you want the richest dialog (multi-select plus fine-grained filters), FileDialog is the answer. If you just need one file path to read, GetOpenFilename is a single line and returns the path directly — no object, no .SelectedItems. If you need a path to save to, that is GetSaveAsFilename. All three obey the same rule: they give you a path, and opening or saving it is still your next line.

How ExcelMaster helps

The two mistakes on this page are silent ones: reading .SelectedItems after the user cancelled (a run-time error), and forgetting the trailing backslash when you build a path from a chosen folder (a file written to the wrong place). Both are easy to write and annoying to trace.

ExcelMaster lets you describe the step in plain words — "let me pick a folder, then open every workbook in it" — and it writes the FileDialog code that guards the cancel, normalises the path, and loops the files correctly. You keep the workbook and the code, and you skip the off-by-one.

Frequently asked questions

How do I open a file dialog in Excel VBA?

Create the object with Set fd = Application.FileDialog(msoFileDialogFilePicker), configure it (.Title, .Filters, .AllowMultiSelect), then call fd.Show. .Show returns -1 if the user picked something and 0 if they cancelled. Read the chosen path from fd.SelectedItems(1). Note that this only returns the path — you still call Workbooks.Open to open it.

How do I let the user select a folder in VBA?

Use Application.FileDialog(msoFileDialogFolderPicker). This is the only file dialog that returns a folder rather than a file — GetOpenFilename cannot do it. After If fd.Show = -1 Then, read fd.SelectedItems(1) for the folder path, and append a backslash before you add a filename, because the folder comes back without a trailing \.

Why does FileDialog.Show return -1 instead of the file path?

Because .Show returns a status, not the path: -1 means the user made a selection, 0 means they cancelled. The selected path (or paths) live in the .SelectedItems collection. Test If fd.Show = -1 Then first, and only read .SelectedItems(1) when it is -1, because after a cancel that collection is empty and indexing it raises an error.

How do I select multiple files with FileDialog?

Set .AllowMultiSelect = True before calling .Show. When the user picks several files, loop For i = 1 To fd.SelectedItems.Count and read each path with fd.SelectedItems(i). The collection is 1-based, so start the loop at 1, not 0 — starting at 0 raises "Subscript out of range."

What is the difference between FileDialog and GetOpenFilename?

GetOpenFilename is a one-line function that returns a file path string (or False on cancel) — simplest when you just need one file to read. FileDialog is a configurable object that can also pick folders, do richer multi-select, and (via .Execute) open or save directly. Use GetOpenFilename for the common case, FileDialog when you need a folder or the extra control.

Tested in

Tested in: Excel 365 (Windows 11), VBA 7.1 — last verified 2026-09-26.