Browse for a File

This function will allow you to make use of Windows browse for a file dialog window, to allow your users to locate where a file is. 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 start_path, and this tells the function what folder to default to when browsing (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 start_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, file_type, specifies the file type to be searched for (e.g. “txt”, “csv”, “xls” etc). I have not made this an optional argument because in the vast majority of applications for this function it would not make sense. However, the function should be easy to adapt to do this.

The third 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 file X”. 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 file path browsed for, as a string. If no file is selected, it will return an empty string (“”).

Function BrowseFile(start_path As String, file_type As String, title As String) As String
    
    ' Purpose -->       Allows the user to browse for a file, starting at either the
    '                   provided folder or windows current active folder. If no file is
    '                   found or browsed for, an empty string is returned.
    ' Arguments -->     start_path (String)
    '                   - Holds the path to the folder to start searching in.
    '                   - 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.
    '                   file_type (String)
    '                   - Holds what type of file to search for. Accepts any file extension
    '                     title (String)
    '                   - Holds what the browse window title should be
    ' Returns -->       (String)
    '                   - Returns the path and name of the file browsed for.
    '                   - If no file is browsed for, an empty string is 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 file dialog window
    Dim dialog_return As String
    
    ' Set the folder dialog window
    Set file_dialog = Application.FileDialog(msoFileDialogFilePicker)
    
    ' Switch off the ability to select multiple files
    file_dialog.AllowMultiSelect = False
    
    ' Was a file type set
    If Trim(file_type) <> "" Then
        
        ' Build a default file filter
        file_type = "*." & LCase(file_type)
    
    End If
    
    ' Prepare start_path
    ' ==================
    
    ' If there isn't a '\' in the path, the path isn't valid so set the path to ""
    If InStr(1, start_path, "\") = 0 Then existing_path = ""
    
    ' Make sure the file path isnt empty
    If Len(start_path) > 0 Then
        
        ' Put the last 5 characters of the file path in a string
        temp_str = Right(start_path, 5)
        
        ' Is there "." in the temp_str, it it is then there must a file extension
        If InStr(1, temp_str, ".", vbTextCompare) > 0 Then
        
            ' If there is a file extension, we know to remove the file name from the path
            start_path = Left(start_path, InStrRev(start_path, "\") - 1)

        End If
        
        ' If there isnt a "\" at the end of the file path add it back in
        If Right(start_path, 1) <> "\" Then start_path = start_path & "\"
    
    End If

    ' Browse for file
    ' ===============
    
    ' Open the file browser dialog at the given start location
    file_dialog.InitialFileName = start_path
    
    ' Was a title for the file dialog provided
    If title <> "" Then
        
        ' Add the title to the file dialog window
        file_dialog.title = title
    
    End If
    
    ' Work with the dialog window
    With file_dialog
        
        ' Clear any previous filters and add the selected filters
        .Filters.Clear
        .Filters.Add file_type, file_type
        
        ' Set the selected item to a default blank value
        dialog_return = ""
        
        ' If an item was selected put in the dialog_return variable
        If .Show = -1 Then dialog_return = .SelectedItems.Item(1)
                
    End With
    
    ' Finish up
    ' =========
    
    ' Return the result
    BrowseFile = 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 *