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
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
VBA Code To Delete All Shapes On A Excel sheet Here is a VBA code which deletes all the shapes from an Excel sheet. Code is simple but you have to be bit careful while…
VBA Code to send Outlook Emails Sending bulk emails is a very common activity, there are many office activities that need a person to send bulk emails to single or multiple recipients. You also may…
VBA to Read Excel Data Using Connection String Sometimes as a programmer you need to read heavy (more then 5 MB) Excel files. There are two ways you can read data from Excel files: Open…
How to Find Duplicate Files In excel using VBA? Yesterday I was working on my computer and cleaning the drives to make some more space. I was surprised to see so many files saved at…
VBA Code To Change Cell Color Excel supports more than 16 million colors in a cell; hence you should know how to set the exact color in a cell. To do this, you can use…
VBA Code to list Files in Folder To work on multiple files through VBA programming, you need to have VBA code that can list files in a folder. In this article we will learn three…