VBA Code to Browse a Folder

Quite often a VBA developer requires code to browse a folder. This is mainly for saving the output file or reading the input file(s). Below is the VBA code that can be used to browse a file folder.

Public Sub PickAFolder()
    'Microsoft Office XX.X Object Library is required to run this code
    'Variable declaration
    Dim objFileDialog As FileDialog
    Dim objSelectedFolder As Variant
    'Browse a folder
    Set objFileDialog = Application.FileDialog(msoFileDialogFolderPicker)
    With objFileDialog
        .ButtonName = "Select"
        .Title = "Select a folder"
        .InitialView = msoFileDialogViewList
        .Show
        For Each objSelectedFolder In .SelectedItems
            'Show the selected folder details on Excel sheet
            Sheet1.Range("B6").Value = objSelectedFolder
        Next
    End With
End Sub

It is worth to mention that you need to add Office reference (Microsoft Office XX.X Object Library) in Excel VBA from Menu Bar (Tools>References…). Below are the steps to add Office reference in Excel VBA:

1. From the Menu Bar, click on Tools > References…

2. Select ‘Microsoft Office XX.X Object Library’ and click on ‘OK’ button

To use this code in your Excel file, follow below steps:

1. Open an Excel file
2. Press Alt+F11
3. Insert a Module (Insert>Module) from menu bar
4. Paste the code in the module
5. Now add a shape in Excel sheet
6. Give a name to the shape like ‘Browse a Folder’

7. Right click on the shape and select ‘Assign Macro…’

8. Select PickAFolder from the list and click on ‘Ok’ button

9. Done