Mashhura Jahan
The objective of this article is to explain how you can use Excel VBA to open folder and select file. You can open a file dialog box in a specific location to open a folder and select a file or you can browse to open a folder and select a file.
Download Practice Workbook
4 Suitable Examples to Open Folder and Select File Using Excel VBA
Here, I will show you 3 different methods of using Excel VBA to open folder and select file. I will show you various examples.
1. Using GetOpenFilename to Open Folder and Select File
In this first method, I will use the GetOpenFilename method to open a file dialog box that will help you to browse to the folder you want and then open the folder and select the file. I will explain 2 examples of how you can write the code.
Example-01: Setting Variable as Variant
In this first example, I will use the variable as a Variant. Let’s see step by step how it is done.
Steps:
- Firstly, go to the Developer tab.
- Secondly, select Visual Basic.
Now, you will see the Visual Basic window has opened.
- Firstly, select the Insert tab.
- Secondly, select Module.
Here, a Module will open.
- After that, in that Module write the following code.
Sub select_file()
Dim File_text As Variant
File_text = Application.GetOpenFilename("All xlsx files (*.xlsx*), *.xlsx", , "Select Your File")
Workbooks.Open Filename:=File_text
End Sub
Code Breakdown
- Here, I created a Sub Procedure named select_file.
- Then. I declared a variable named File_text as Variant.
- Next, I used a built-in method of Application object which is GetOpenFilename. This method will return a dialog box so that you can browse and select file.
- After that, in the GetOpenFilename method, I customized the FileFilter, FilterIndex and Title of the dialog box. Here, I filtered for Excel files but you can filter for any other file type. And the title of my dialog box will be “Select Your File”.
- Then, I used Workbooks.Open Filename method which will help to open the selected file.
- Finally, I ended the Sub Procedure.
- Now, save the code.
- Finally, Run the code.
Here, you will see a dialog box with your selected title will appear.
- Now, browse the folder you want to open.
- Then, select the file you want to open. Here, I selected an Excel file named Fitting Sheet on One Page.
- After that, select Open if you want to open the selected file.
Finally, you will see your selected Excel file has opened.
Example-02: Setting Variable as String
In this example, I will use the variable as a String. Let’s see step by step how you can write the VBA code.
Steps:
- Firstly, go to the Developer tab.
- Secondly, select Visual Basic.
Now, you will see the Visual Basic window has opened.
- Firstly, select the Insert tab.
- Secondly, select Module.
Here, you will see a Module will open.
- Next, in that Module write the following code.
Sub OpenFile()
Dim Open_file As String
Open_file = Application.GetOpenFilename(Title:="Browse & Select File", FileFilter:="All Excel Files (*.xls*), *xls*")
Workbooks.Open Filename:=Open_file
End Sub
Code Breakdown
- Here, I created a Sub Procedure named OpenFile.
- Then. I declared a variable named Open_file as String.
- Next, I used a built-in method of Application object which is GetOpenFilename. This method will return a dialog box so that you can browse and select files.
- After that, in the GetOpenFilename method, I declared the Title and FileFilter. Here, I filtered for Excel files but you can filter for any other file type. And the title of my dialog box will be “Browse & Select File”.
- Then, I used Workbooks.Open Filename method which will help to open the selected file.
- Finally, I ended the Sub Procedure.
- Now, save the code.
- After that, Run the code.
At this point, you will see a dialog box has opened.
- Now, browse the folder you want and select the file. Here, I selected an Excel file named Using Pivot Chart.
- Then, If you want you can open your selected file by clicking on OK.
Read More: How to Open Workbook with Variable Name with Excel VBA
2. Use of Command Button to Open Folder and Select File
In this method, I will use the FileDialog method from the Application object to open a dialog box for a specific folder in Excel VBA. Here, for the dialog box, I will set an initial location where it will open then you can also browse other folders if you want.
For this example, I will use a Command Button to write the code. Let’s see how it is done.
Steps:
- Firstly, go to the Developer tab.
- Secondly, select Insert.
Here, a drop-down menu will appear.
- Thirdly, select Command Button from ActiveX Controls.
- Now, click and drag your mouse cursor where you want your Command Button.
Here, you will see your Command Button has been inserted into your Excel sheet.
- After that, Right-click on the Command Button.
- Next, select Properties.
- Now, change the caption as you want to see on your Command Button. Here, I wrote, “Click Here to Open File”.
In the following picture, you can see the caption has been updated.
- After that, double-click on the command button to open a Module.
Here, you will see a Module that will open with a Private Sub Procedure named CommandButton1_Click.
- Now, in that Module write the following code.
Private Sub CommandButton1_Click()
Dim file_dialog_box As Office.FileDialog
Set file_dialog_box = Application.FileDialog(msoFileDialogFilePicker)
With file_dialog_box
.Filters.Clear
.Title = "Select Your Excel File"
.Filters.Add "All Excel Files", "*.xlsx?", 1
.AllowMultiSelect = False
.InitialFileName = "E:\office\Excel Files"
Dim selected_file As String
If .Show = True Then
selected_file = .SelectedItems(1)
End If
End With
If selected_file <> "" Then
Workbooks.Open selected_file
End If
End Sub
Code Breakdown
- Here, a Private Sub Procedure was already created by the CommandButton. A Private Sub is only applicable for that specific sheet it is on.
- Then, I declared a variable named file_dialog_box as Office.FileDialog.
- After that, I used the Set statement to assign Application.FileDialog method to file_dialog_box.
- Next, in the FileDialog method, I selected msoFileDialogFilePicker as fileDialogType.
- Then, I used a With statement to avoid repeating the same object name. At first, I cleared the applied filters on the dialog box using Filters.Clear property.
- After that, I used the Tiltle property to add a title to the dialog box.
- Next, I used Filters.Add property to add filters.
- Then, I used the AllowMultiSelect property to define if you can select one or more than one file. Here, I set the value as “False”. But, you can set the value as “True” if you want to select multiple files.
- After that, I used the InitialFilename property to select the initial folder location.
- And then, I declared a variable named selected_file as String.
- Then, I used an IF statement to show the file.
- Next, I ended the With Statement.
- After that, I used another IF statement to open the file. It will check if there is any selected_file and then open it using Workbooks.Open method.
- And then, I ended the IF statement.
- Finally, ended the Sub Procedure.
- Now, save the code and go back to your worksheet.
- After that, select the Command Button to open the folder.
Now, you will see the folder in your selected location has opened.
- After that, select the file you want to open.
- If you want you can open your selected file by clicking on OK.
Read More: How to Open File Dialog Default Folder with Excel VBA (3 Ways)
3. Applying Cell Reference to Open Folder and Select File
In this method, I will use the FileDialog method from the Application object to open a dialog box for a specific folder in Excel VBA. Here, for the dialog box, I will set an initial location where it will open then you can also browse other folders if you want. Here, I will use a cell reference in this example to define the initial path. In the following picture, you can see that I have written a folder path in cell C9.
Let’s see how it is done.
Steps:
- Firstly, go to the Developer tab.
- Secondly, select Visual Basic.
Now, you will see the Visual Basic window has opened.
- Firstly, select the Insert tab.
- Secondly, select Module.
Here, you will see a Module will open.
- Now, in that Module write the following code.
Sub Select_File_withInitialPath()
Dim file_dialog_box As Office.FileDialog
Dim select_file As String
Dim my_workbook As Workbook
Dim my_worksheet As Worksheet
Set my_workbook = ThisWorkbook
Set my_worksheet = my_workbook.Worksheets("Use of FileDialog")
Set file_dialog_box = Application.FileDialog(msoFileDialogFilePicker)
With file_dialog_box
.Filters.Clear
.Filters.Add "Excel Files", "*.xlsx?", 1
.Title = "Choose Your Excel file"
.AllowMultiSelect = False
.InitialFileName = my_worksheet.Range("C9").Value
If .Show = True Then
select_file = .SelectedItems(1)
End If
End With
If select_file <> "" Then
Workbooks.Open select_file
End If
End Sub
Code Breakdown
- Here, I created a Sub Procedure named Select_File_withInitialPath.
- Then, I declared a variable named file_dialog_box as Office.FileDialog, a 2nd variable named select_file as String, a 3rd variable named my_workbook as Workbook, and another variable named my_worksheet as Worksheet.
- After that, I used a Set statement to assign ThisWorkbook as my_workbook, and another Set statement to assign a worksheet named “Use of FileDialog” as my_worksheet.
- After that, I used the Set statement to assign Application.FileDialog method to file_dialog_box.
- Next, in the FileDialog method, I selected msoFileDialogFilePicker as fileDialogType.
- Then, I used a With statement to avoid repeating the same object name.
- At first, I cleared the applied filters on the dialog box using Filters.Clear property.
- After that, I used Filters.Add property to add filters.
- Next, I used the Tiltle property to add a title to the dialog box.
- Then, I used the AllowMultiSelect property to define if you can select one or more than one file. Here, I set the value as “False”. But, you can set the value as “True” if you want to select multiple files.
- After that, I used InitialFilename property to select the initial folder location. Here, I used a cell reference from my_worksheet to give the value.
- Then, I used an IF statement to show the file.
- Next, I ended the With statement.
- After that, I used another IF statement to open the file. It will check if there is any select_file and then open it using Workbooks.Open method.
- And then, I ended the IF statement.
- Finally, ended the Sub Procedure.
- Now, save the code.
- After that, Run the code.
Now, you will see the folder in your selected path has opened.
- After that, select the file you want.
- Then, If you want you can open your selected file by clicking on OK.
Read More: How to Open Workbook as Read-Only with Excel VBA
4. Using Excel VBA to Open Folder of Active Workbook
In this method, I will explain how you can open the folder of your active worksheet and then select other files from that folder using Excel VBA.
Let’s see how you can use Excel VBA to open folder and select file.
Steps:
- Firstly, go to the Developer tab.
- Secondly, select Visual Basic.
Now, you will see the Visual Basic window has opened.
- Firstly, select the Insert tab.
- Secondly, select Module.
Here, you will see a Module will open.
- Next, in that Module write the following code.
Sub Open_act_Wb_folder()
Dim file_dialog_box As Office.FileDialog
Dim select_file As String
Set file_dialog_box = Application.FileDialog(msoFileDialogFilePicker)
With file_dialog_box
.Filters.Clear
.Filters.Add "All Excel Files", "*.xlsx?", 1
.Title = "Choose Your Excel file"
.AllowMultiSelect = False
.InitialFileName = Application.ActiveWorkbook.Path
If .Show = True Then
select_file = .SelectedItems(1)
End If
If select_file <> "" Then
Workbooks.Open select_file
End If
End With
End Sub
Code Breakdown
- Here, I created a Sub Procedure named Open_act_Wb_folder.
- Then, I declared a variable named file_dialog_box as Office.FileDialog, and another variable named select_file as String
- After that, I used the Set statement to assign Application.FileDialog method to file_dialog_box.
- Next, in the FileDialog method, I selected msoFileDialogFilePicker as fileDialogType.
- Then, I used a With statement to avoid repetition of the same object name. At first, I cleared the applied filters on the dialog box using Filters.Clear property.
- After that, I used Filters.Add property to add filters.
- Next, I used the Tiltle property to add a title to the dialog box.
- Then, I used the AllowMultiSelect property to define if you can select one or more than one file. Here, I set the value as “False”. But, you can set the value as “True” if you want to select multiple files.
- After that, I used the InitialFilename property to select the initial folder location. Here, I used Application.ActiveWorkbbok.Path method to set the path of the active workbook as initial folder location.
- Then, I used an IF statement to show the file.
- After that, I used another IF statement to open the file. It will check if there is any select_file and then open it using Workbooks.Open method.
- And then, I ended the IF statement.
- After that, I ended the With statement.
- Finally, ended the Sub Procedure.
- Now, save the code.
- After that, Run the code.
Now, you will see the folder of your current active workbook has opened.
- Here, select the file you want to open.
- After that, If you want you can open your selected file by clicking on OK.
Read More: How to Open Workbook and Run Macro Using VBA (4 Examples)
Opening Selected File from Folder Path
In this section, I will explain how you can open an Excel file directly in a specific location using Excel VBA. Here, I have written the file path in my Excel sheet. I will use this cell reference to select the file location.
Here, you can see I have an Excel file named test file 1 at that location. I will open this file using Excel VBA.
Let’s see how it is done.
Steps:
- Firstly, go to the Developer tab.
- Secondly, select Visual Basic.
Now, you will see the Visual Basic window has opened.
- Firstly, select the Insert tab.
- Secondly, select Module.
Here, a Module will open.
- Now, in that Module write the following code.
Sub Open_selected_file()
Dim my_workbook As Workbook
Dim my_worksheet As Worksheet
Dim folder_path As String
Dim my_file As String
Dim selected_file As Workbook
Set my_workbook = ThisWorkbook
Set my_worksheet = my_workbook.Worksheets("Employing Workbooks.Open")
folder_path = my_worksheet.Range("C9").Value
my_file = Dir(folder_path & "\*.xlsx")
Set selected_file = Workbooks.Open(folder_path & "\" & my_file)
End Sub
Code Breakdown
- Here, I created a Sub Procedure named Open_selected_file.
- Then, I declared a variable named my_workbook as Workbook, a 2nd variable named my_worksheet as Worksheet, and another variable named folder_path as String.
- After that, I declared a variable named my_file as String and another variable named selected_file as Workbook.
- Next, I used a Set statement to assign ThisWorkbook as my_workbook, and another Set statement to assign a worksheet named “Employing Workbooks.Open” as my_worksheet.
- Then, I defined the folder_path using cell reference from my_worksheet.
- After that, I used the Dir function to select my_file. The Dir() function will return the first file that matches the path.
- Then, I used Workbooks.Open method the open my_file as the selected_file.
- Finally, I ended the Sub Procedure.
- Now, save the code.
- After that, Run the code.
Here, you will see the selected Workbook has opened from the given path.
Read More: How to Open Workbook from Path Using Excel VBA (4 Examples)
Things to Remember
- Whenever working with Excel VBA, you must save the file as Excel Macro-Enabled Workbook.
Conclusion
In this article, I tried to explain how to use Excel VBA to open folder and select file. Here, I covered 3 different methods with different examples. I hope this was helpful for you. To get more articles like this visit ExcelDemy. If you have any questions please, let me know in the comment section below.
Je suis resté perplexe, vous êtes magnifique,j’ai trouvé ce que je cherche.Bravo
Je vous souhaite la réussite dans cette vie et un paradis à l’avenir.merci bcp
Hello AMGHAR,
Thanks for your feedback.
If you face any further problems, please share your Excel file with us in the comment section.
Regards
Arin Islam,
Exceldemy.