Browse for a Folder

This function will allow you to make use of Windows browse for a folder dialog window, to allow your users to locate where there folders are. I have tried to make this function as safe as possible when passing it the arguments it needs.

The first argument the function needs is an existing_path, and this tells the function what folder to default to (e.g. “C:\MyFolder\”). It is perfectly safe to pass this argument an empty string (“”), and this will tell the function to use the last folder browsed for in Windows.

If you put a file path in the existing_path argument (e.g. “C:\MyFolder\MySpreadsheet.xls”) it will ignore the file name and start at the folder where the file is (e.g. “C:\MyFolder\”).

The second argument, title, tells the Windows folder dialog window what title to show in its title bar. For example your could set this argument to “Browse for my folder”. Again, this argument is perfectly safe to pass an empty string (“”) , and this will simply tell the function to use the windows default title for the dialog window.

The tool will return the folder path browsed for as a string. If no folder is selected, it will return the existing_path.

Function BrowseFolder(existing_path As String, title As String) As String
    
    ' Purpose -->       Allows the user to browse for a folder, starting at either the provided
    '                   folder or windows current active folder. If no folder is found or browsed
    '                   for, the original starting folder is returned instead.
    ' Arguments -->     existing_path (String)
    '                   - Holds the path to the folder to start searching for.
    '                   - If passed a file path including a filename, the filename will be ignored.
    '                   - If passed a blank string the browsing will start at the last folder
    '                     accessed in file explorer.
    '                   title (String)
    '                   - Holds the title to be shown in the folder browser dialog
    ' Returns -->       (String)
    '                   - Returns the path of the folder browsed for.
    '                   - If no folder is browsed for, the existing_path will be returned
    ' Created by -->    Carl Nixon, Excel-Expert.co.uk
    
    ' Set up variables
    ' ================
    
    ' Holds an instance for the folder dialog window
    Dim file_dialog As FileDialog
    
    ' Holds the value returned by the folder picker dialog window
    Dim dialog_return As String
    
    ' Open the folder dialog window
    Set file_dialog = Application.FileDialog(msoFileDialogFolderPicker)
    
    ' Prepare existing_path
    ' =====================
    
    ' If there isn't a '\' in the path, the path isn't valid so set the path to ""
    If InStr(1, existing_path, "\") = 0 Then existing_path = ""
    
    ' Make sure the file path isnt empty
    If Len(existing_path) > 0 Then
        
        ' If exisiting_path is a full file path (not a folder path)
        If Right(existing_path, 1) <> "\" Then
        
            ' If it is a folder path, remove the file name part
            existing_path = Left(existing_path, InStrRev(existing_path, "\") - 1)
        
        End If
                                                                                        
    End If
    
    ' Make sure folder path ends in a '\'
    If Right(existing_path, 1) <> "\" Then existing_path = existing_path & "\"
    
    ' Set up and open the file dialog window
    ' ======================================
    
    ' Was the dialog title set?
    If Trim(title) <> "" Then
        
        ' Set the dialog title
        file_dialog.title = title
    
    End If
    
    ' Work with the dialog window
    With file_dialog
    
        ' Set the dialog windows settings
        .AllowMultiSelect = False
        .InitialFileName = existing_path
        
        ' Breakout if not file found
        If .Show <> -1 Then GoTo NotFound
        
        ' Grab the selected values
        dialog_return = .SelectedItems(1)
    
    End With
NotFound:

    ' Tidy up the results
    ' ===================
    
    ' If nothing was selected, then revert to the original path
    If dialog_return = "" Then dialog_return = existing_path
    
    ' Make sure the selected path isnt empty
    If Len(dialog_return) > 0 Then
        
        ' If the result doesn't end in a '\' add a '\'
        If Right(dialog_return, 1) <> "\" Then dialog_return = dialog_return & "\"
    
    End If
    
    ' Finish up
    ' =========
    
    ' Make sure empty returns are really empty
    If dialog_return = "\" Then dialog_return = ""
    
    ' Clear up the memory
    Set file_dialog = Nothing
    
    ' Return the result
    BrowseFolder = dialog_return
    
End Function

All of the code featured on this site is available to download on my GitGub page. Click here to visit it.

Carl Nixon

Carl Nixon is a VBA developer with 20 years experience, 10 years as a freelancer. He has worked for clients around the world, ranging in size from micro businesses to global brands. In his 20 years developing VBA projects he has helped businesses to automate and simplify processes, resulting in massive time and money savings. He is also the author of 3 top selling books on Excel. Visit my LinkedIn profile or my GitHub page

Leave a Reply

Your email address will not be published. Required fields are marked *