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 SubVBA Code to Browse a Folder Steps:
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 the ‘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
