I came across a nice reusable function, FSBrowse, to utilize the FileDialog to allow your users to select files/folders with ease.

That said, there was one major drawback, the fact that it necessitates a reference be set to Microsoft Office XX.X Object Library.  Now, the more experience I gain, the more I know that Early binding (requiring set references) is detrimental to any project!  So I set out to modify NeoPa’s nice function so it was Late Bound and thus didn’t require any reference libraries, making it completely portable into any project without any changes.

I did some digging and some testing and eventually came up with the following:

Public Enum msoFileDialogType
    msoFileDialogOpen = 1           'Open Button
    msoFileDialogSaveAs = 2         'Save As Button with Overwrite Confirmation
    msoFileDialogFilePicker = 3     'Ok Button
    msoFileDialogFolderPicker = 4   'Ok Button
End Enum
 
 
'---------------------------------------------------------------------------------------
' Procedure : fFileDialog
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Resuable FileDialog function
' Based on  : NeoPa's FSBrowse Public Function
'             https://bytes.com/topic/access/insights/916710-select-file-folder-using-filedialog-object
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
'             (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: Late Binding  -> None required
'             Early Binding -> Microsoft Office XX.X Object Library
'Resources  : https://msdn.microsoft.com/EN-US/library/office/ff862446.aspx
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' lDialogType   :
' sTitle        : Title to give the File Dialog
' sInitFileName : The initial folder/path to initially open in
' bMultiSelect  : Allow the selection of more than 1 file
' sFilter       : Filter of file type(s) to display/allow for selection
'
' Usage:
' ~~~~~~
' fFileDialog(msoFileDialogFilePicker,,,,"MS Excel,*.xlsx;*.xls~MS Word,*.doc;*.docx")
' fFileDialog(msoFileDialogFilePicker, "Which database do you wish to link to?", "C:\tmp\",,"MS Access,*.accdb;*.mdb")
' fFileDialog(msoFileDialogFilePicker)
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2016-03-03              Initial Release
' 2         2020-01-22              Header updating
' 3         2022-10-10              Handles multiselect now and return a collection
' 4         2023-03-25              Added code to reset the CurDir because of a bug that
'                                   the FileDialog changes the path and lock the folder
' 5         2023-03-31              Added Conditional Compilation
'                                   Updated Header
'---------------------------------------------------------------------------------------
Public Function fFileDialog(Optional ByRef lDialogType As msoFileDialogType = msoFileDialogFilePicker, _
                            Optional sTitle As String = "", _
                            Optional sInitFileName = "", _
                            Optional bMultiSelect As Boolean = True, _
                            Optional sFilter As String = "All Files,*.*") As VBA.Collection
    On Error GoTo Error_Handler
    #Const FD_EarlyBind = False    'True => Early Binding / False => Late Binding
    #If FD_EarlyBind = True Then
        Dim oFd               As Office.FileDialog      'FileDialog Object
    #Else
        Dim oFd               As Object     'FileDialog Object
        Const msoFileDialogViewList = 1
        Const msoFileDialogViewDetails = 2      'Enum MsoFileDialogView
    #End If
    Dim vItems                As Variant    'Files/Folders
    Dim vFilter               As Variant
    Dim sCurDir               As String

    sCurDir = CurDir
    Set fFileDialog = New VBA.Collection

    Set oFd = Application.FileDialog(lDialogType)
    With oFd
        'Set the dialog's title
        If sTitle = "" Then
            Select Case lDialogType
                Case msoFileDialogOpen
                    .Title = "Select the File to open"
                Case msoFileDialogSaveAs
                    .Title = "Select the Path and Filename to SaveAs"
                Case msoFileDialogFilePicker
                    .Title = "Select the File(s)"
                Case msoFileDialogFolderPicker
                    .Title = "Select a Folder"
            End Select
        Else
            .Title = sTitle
        End If
        'Set the initial folder to open in
        If sInitFileName <> "" Then .InitialFileName = sInitFileName
        'Type of view to display, I'm defaulting to a detailed view
        .InitialView = msoFileDialogViewDetails    'msoFileDialogViewList, ...
        'Set any specified filters
        If lDialogType <> msoFileDialogFolderPicker And lDialogType <> msoFileDialogSaveAs Then
            .AllowMultiSelect = bMultiSelect    'Single or Multiple selection
            Call .Filters.Clear    'Clear any existing filters
            'as they remain in memory and are cummulative
            For Each vFilter In Split(sFilter, "~")    'Add our filters, 1 by 1
                Call .Filters.Add(Split(vFilter, ",")(0), Split(vFilter, ",")(1))
            Next vFilter
        End If

        If .Show = True Then
            For Each vItems In .SelectedItems
                fFileDialog.Add vItems
            Next
        End If
    End With

Error_Handler_Exit:
    On Error Resume Next
    If sCurDir <> CurDir Then ChDir (sCurDir)
    If Not oFd Is Nothing Then Set oFd = Nothing
    Exit Function

Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: FileDialogMulti" & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Function

Usage Examples

Then to use it, you would simply do something like:

Multi-select files of any type

Sub TestFileDialog1()
    Dim oFilesCol             As Collection
    Dim sFile                 As Variant

    Set oFilesCol = fFileDialog(msoFileDialogFilePicker, "Pick Your Files", "C:\Temp\", True)

    Debug.Print oFilesCol.Count & " file(s) selected"
    'Do something with each file that the user selected
    For Each sFile In oFilesCol
        Debug.Print "'" & sFile & "'"
    Next
    
    Set oFilesCol = Nothing
End Sub

Multi-select files, but only display and allow picking of accdb and mdb database files

Sub TestFileDialog2()
    Dim oFilesCol             As Collection
    Dim sFile                 As Variant

    Set oFilesCol = fFileDialog(msoFileDialogFilePicker, "Pick Your Files", "C:\Temp\", True, "MS Access Databases,*.accdb;*.mdb")

    Debug.Print oFilesCol.Count & " file(s) selected"
    'Do something with each file that the user selected
    For Each sFile In oFilesCol
        Debug.Print "'" & sFile & "'"
    Next
    
    Set oFilesCol = Nothing
End Sub

Single file select, but only display and allow picking of xls or xlsx Excel workbooks

Sub TestFileDialog3()
    Dim oFilesCol             As Collection
    Dim sFile                 As Variant

    Set oFilesCol = fFileDialog(msoFileDialogFilePicker, "Pick a File", , False, "MS Access Excel,*.xls;*.xlsx")

    Debug.Print oFilesCol.Count & " file(s) selected"

    '    'Do something with the selected file
    'Option 1
    '~~~~~~~~~~~~~~~~~~~~~~~~~~~
    For Each sFile In oFilesCol
        Debug.Print "'" & sFile & "'"
    Next
    '*****************
    'OR
    '*****************
    'Option 2
    '~~~~~~~~~~~~~~~~~~~~~~~~~~~
    If oFilesCol.Count <> 0 Then Debug.Print "'" & oFilesCol(1) & "'"

    Set oFilesCol = Nothing
End Sub

Folder Dialog – Select a Folder

Sub TestFileDialog4()
    Dim oFolderCol            As Collection
    Dim sFolder               As Variant

    Set oFolderCol = fFileDialog(msoFileDialogFolderPicker, "Pick a Folder")

    'Option 1
    '~~~~~~~~~~~~~~~~~~~~~~~~~~~
    For Each sFolder In oFolderCol
        Debug.Print "'" & sFolder & "'"
    Next
    '*****************
    'OR
    '*****************
    'Option 2
    '~~~~~~~~~~~~~~~~~~~~~~~~~~~
    If oFolderCol.Count <> 0 Then Debug.Print "'" & oFolderCol(1) & "'"

    Set oFolderCol = Nothing
End Sub

File Open Dialog – Multiple File Selection

Sub TestFileDialog5()
    Dim oFilesCol             As Collection
    Dim sFile                 As Variant

    Set oFilesCol = fFileDialog(msoFileDialogOpen, "Pick The File To Launch", , True)

    Debug.Print oFilesCol.Count & " file(s) selected"

    'Option 1
    '~~~~~~~~~~~~~~~~~~~~~~~~~~~
    For Each sFile In oFilesCol
        Debug.Print "'" & sFile & "'"
    Next
    '*****************
    'OR
    '*****************
    'Option 2
    '~~~~~~~~~~~~~~~~~~~~~~~~~~~
    If oFilesCol.Count <> 0 Then Debug.Print "'" & oFilesCol(1) & "'"

    Set oFilesCol = Nothing
End Sub

File Save As Dialog – Single File Selection

Sub TestFileDialog6()
    Dim oFilesCol             As Collection
    Dim sFile                 As Variant

    'Save As Dialog with confirmation to Overwrite the file if it already exists
    Set oFilesCol = fFileDialog(msoFileDialogSaveAs, "Pick The File To Launch", , False)

    Debug.Print oFilesCol.Count & " file(s) selected"
    If oFilesCol.Count <> 0 Then Debug.Print "'" & oFilesCol(1) & "'"

    Set oFilesCol = Nothing
End Sub

File Dialog Folder Locking Bug

You’ll notice that I recently updated my code in late March, 2023, after a recent MVP discussion started by Geoff Griffith. I had experienced such issues in the past, but never troubleshooted to determine that the cause was the FileDialog call. That said, Geoff did, and provided repo step to show the cause to effect and root cause of the folder locking was in fact the use of File Dialog!

To sum up the issue, undocumented, is the fact that when you use FileDialog it change the Application’s CurDir thus locking the folder until the CurDir is changed or the application is closed. Now, in most cases this truly doesn’t pose much of an issue, but if you are working with temporary files/folders and try to delete them, rename them, … you won’t be able to and will raise errors.

The solution, as provided in my code above, is to simply grab the current CurDir prior to using FileDialog and set it back, using ChDir, once completed. So a simple fix.

The bigger issues are:

• Why isn’t this documented!
• Why is FileDialog locking anything in the first place when all it does is return a file/folder listing?!

Yes, Microsoft has been made aware of the issue.

That pretty much sums it up, but if you want more information on the subject, Mike Wolfe has written up a more extensive article on the subject, so feel free to review it:

Microsoft Access’s Surprising Folder Locking Behavior

Microsoft Access could be the source of file and folder locks in ways that might surprise you.

Download a Demo Database

Feel free to download a 100% unlocked copy by using the link provided below:

Disclaimer/Notes:

Microsoft Access 2010 Runtime
Microsoft Access 2013 Runtime
Microsoft Access 2016 Runtime
Microsoft 365 Access Runtime

Resources on the Subject

FileDialog object (Office)

Provides file dialog box functionality similar to the functionality of the standard Open and Save dialog boxes found in Microsoft Office applications.