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
How to use VBA to open Workbook in Excel? There are few VBA codes which are commonly used by every developer. One of them is giving an option to user to browse a file. Below is a…
VBA Code to Read Outlook Emails Reading emails from Outlook and capture them in Excel file is very common activity being performed in office environment. Doing this activity manually every time is quite boring and…
Have you ever felt that Microsoft should have added a formula in Excel which can count the cells based on specific color? I have seen many code requests to share a VBA code that can count the cells by its color. To help our subscribers and developers, we are sharing 2 codes that be used to count the cells with specific color and returns the count of the matching color cells.
VBA Code To Add New Sheet In VBA, it is sometime important to add a worksheet at the right place in the Excel. Here is a simple an effective code that adds a new worksheet…
Merge Excel Files From last few months, we have been receiving frequent requests from users for a VBA tool which can consolidate Excel files from a folder. So here we come with one more free…
How to send bulk emails from outlook using excel VBA? Have you ever felt the need of an Excel based VBA tool or code which can help you to draft Outlook emails in bulk by…