Need to learn how to browse for a file path using Excel VBA? VBA can automate many mundane tasks in Excel. It can save a lot of time. If you are looking for such unique tricks, you’ve come to the right place. Here, we will take you through 3 accessible and appropriate examples of browsing for a file path using Excel VBA.
Browse for File Path Using Excel VBA: 3 Examples
This article’s VBA code demonstrates how to let a user choose a file and obtain its path during a subroutine. Here, we’ll use the FileDialog method and the GetOpenFilename method to browse a file path. So let’s explore them one by one.
Example 01: Using FileDialog Method to Browse for File Path in Excel VBA
For the first example, we’ll use the FileDialog method to browse a file path in Excel. Here, we’ll utilize the msoFileDialogFilePicker parameter in the method. So, without further delay, let’s see how to do it.
📌 Steps:
- First of all, make a format like in the image below.
- Here, in cell B2, we’ve inserted a heading.
- Then, in cell B4, write down Path of Selected File and put an arrow symbol.
- Also, create a blank space in cell C4 to show the file path.
- After that, go to the Developer tab.
- Then, select Visual Basic on the Code group.
- Alternatively, press ALT+F11 to do the same task.
- Instantly, the Microsoft Visual Basic for Applications window opens.
- Afterward, move to the Insert tab.
- Later, select Module from the options.
- Immediately, it opens the Code Module.
- Then, write down the following code in the Module.
Sub File_Path()
Dim File_Picker As FileDialog
Dim my_path As String
Set File_Picker = Application.FileDialog(msoFileDialogFilePicker)
File_Picker.Title = "Select a File" & FileType
File_Picker.Filters.Clear
File_Picker.Show
If File_Picker.SelectedItems.Count = 1 Then
my_path = File_Picker.SelectedItems(1)
End If
ActiveSheet.Range("C4").Value = my_path
End Sub
VBA Code Explanation
Sub File_Path()
Dim File_Picker As FileDialog
Dim my_path As String
- Firstly, we create the macro and give it the name File_Path.
- Then, we defined the two variables.
Set File_Picker = Application.FileDialog(msoFileDialogFilePicker)
File_Picker.Title = “Select a File” & FileType
File_Picker.Filters.Clear
File_Picker.Show
- After that, we set the File_Picker variable to use the File Open Dialog box.
- Later, we set the title of the dialog box as Select a File.
- Then, cleared the filters. Which are mainly the file types.
- Finally, we’ve to display the dialog box.
If File_Picker.SelectedItems.Count = 1 Then
my_path = File_Picker.SelectedItems(1)
End If
- Here VBA will check if we have chosen any file, and when it is true it will assign that file path to our my_path variable.
ActiveSheet.Range(“C4”).Value = my_path
End Sub
- The file path stored in the my_path variable shows in cell C4 in the active worksheet.
- Now, return to the worksheet.
- Again, jump to the Insert tab.
- Then, click on the Illustrations group icon.
- After that, click on the Shapes option.
- Lastly, select Rectangle: Rounded Corners from the available shapes.
- Now, take the cursor inside the cell area in the worksheet and it looks like a plus (+) sign.
- Hereafter, expand the plus sign to your convenient location. Hence, hold the mouse clicking while doing this.
- Also, write down Browse for a File inside the shape.
- Moreover, right-click on the shape. Suddenly, a context menu appears.
- Later, select Assign Macro on the list.
- Consequently, the Assign Macro dialog box opens.
- After that, select the macro File_Path.
- Lastly, click OK.
- Now, click on the Browse for a File button.
- Immediately, it opens the Select a File dialog box.
- Then, go to Local Disk (C:).
- Later, select the folder Exceldemy.
- Subsequently, click Open.
- Thus, it opens the Exceldemy folder.
- After that, select the file Sales-Report.
- Lastly, click Open.
- Hence, it stores the file path of the file Sales-Report in cell C4.
Read More: Excel VBA to Open Workbook from Path in Cell
Example 02: Opening Default File Dialog Folder to Browse for File Path in Excel VBA
In this example, we will use the InitialFileName property to change the default folder location to a user-defined location using the Excel VBA. Then, using a code similar to the first method, we will get the file path. So, without further delay, let’s dive in!
📌 Steps:
- At first, open the Code Module as we did in Example 1.
- Then, paste the following code into the Module.
Sub File_Path_Default_Folder()
Dim File_Picker As FileDialog
Dim my_path As String
Set File_Picker = Application.FileDialog(msoFileDialogFilePicker)
File_Picker.Title = "Select a File" & FileType
File_Picker.InitialFileName = "C:\Users\Shahriar Abrar\Desktop\Exceldemy"
File_Picker.Filters.Clear
File_Picker.Show
If File_Picker.SelectedItems.Count = 1 Then
my_path = File_Picker.SelectedItems(1)
End If
ActiveSheet.Range("C4").Value = my_path
End Sub
- Similarly, assign the macro to the Browse for a File button.
- Then, click on the button.
- Thus, it opens the Select a File dialog box.
- Here, we can see that the folder Exceldemy is open as default.
- Now, select Sales-Report and click Open.
- Eventually, it stores the file path in cell C4.
Read More: How to Open Workbook from Path Using Excel VBA
Example 03: Applying GetOpenFilename Method Browse for File Path in Excel VBA
In this example, we will use the VBA GetOpenFilename method to get the file path of a selected file in a specific cell. Allow me to demonstrate the process step-by-step.
📌 Steps:
- At the very beginning, move to the Developer tab.
- Then, click on Insert on the Controls group.
- After that, select Command Button under the ActiveX Controls section.
- Thus, we inserted CommandButton1 into our worksheet.
- Correspondingly, right-click on the button.
- Then, select Properties from the context menu.
- Instantly, the Properties window opens.
- Then, change the Caption to Browse a File.
- Also, click on the three-dot beside the Font option.
- Eventually, it opens the Font wizard.
- Then, select Bold as Font Style.
- Next, select 12 as the Size.
- Lastly, click Ok.
- Again, it returns us to the Properties window.
- Later, close the window.
- At this moment, double-click on the Command Button.
- Suddenly, it opens the Code Module with a portion of code written already.
- Now, write down the remaining code in the Module.
Private Sub CommandButton1_Click()
Dim my_file As String
my_file = Application.GetOpenFilename()
Worksheets("GetOpenFilename").Range("C4").Value = my_file
End Sub
- At this point, return to the worksheet.
- Then, unselect the Design Mode on the Controls group.
- Later, click on the Browse a File button.
- Thus, it opens the Open window.
- Afterward, select Sales-Report from the list.
- Lastly, click Open.
- Similarly, it shows the file path in cell C4.
How to Browse for Folder Path Using Excel VBA
Here, we’ll show you how to browse for a folder path using Excel VBA. Let’s see it in action.
📌 Steps:
- At first, create a button Browse for a Folder as we did in Example 1.
- Also, bring the Code Module just like we did in Example 1.
- Then, paste the following code into the Module.
Sub Folder_Path()
Dim Pick_Folder As FileDialog
Dim my_folder As String
Set Pick_Folder = Application.FileDialog(msoFileDialogFolderPicker)
With Pick_Folder
.Title = "Select A Folder"
.AllowMultiSelect = False
If .Show <> -1 Then Exit Sub
my_folder = .SelectedItems(1) & "\"
End With
MsgBox "Folder Path is: " & my_folder
End Sub
- Next, get back to the worksheet.
- Later, assign the macro to the button like in the previous example.
- Then, select the button Browse for a Folder.
- Eventually, the Select a Folder wizard opens.
- Afterward, select Desktop as location.
- Then, select the Exceldemy folder.
- Finally, click Ok.
- Thus, a message box appears to show the folder path.
Read More: How to Open File Dialog Default Folder with Excel VBA
Download Practice Workbook
You may download the following Excel workbook for better understanding and practice yourself.
Conclusion
This article provides easy and brief solutions to browse for a file path in Excel. Don’t forget to download the Practice file. Thank you for reading this article, we hope this was helpful. Please let us know in the comment section if you have any queries or suggestions.