Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Open Folder and Select File Using Excel VBA (4 Examples)

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.

Excel VBA Open Folder and Select File

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

Using VBA Code to Open Folder and Select File

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.

Open File Dialog Box Using VBA Folder to Select File

  • 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.

Setting Variable as String

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.

Use of Command Button to Open Folder and Select File

  • 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.

Inserting Command Button in Excel VBA to Open Folder and Select File

  • 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.

Applying Cell Reference to Open Folder and Select File

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.

Opening Module to Write VBA Code in Excel to Open Folder and Select File

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.

Using Excel VBA to Open Folder of Active Workbook

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

VBA Code to Open Folder of the Active Workbook and Select File in Excel

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.

Opening Selected File from Folder Path

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.

Opening Visual Basic Window to Open Folder and Select File in Excel VBA

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

VBA Code to Open Selected File from Folder Path in Excel

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.
  • Things to Remember while Open Folder and Select File in Excel VBA

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.


Related Articles

Mashhura

Mashhura

Hey! Welcome to my profile. Right now, I am doing research on Microsoft Excel. I will be posting articles related to this here. My last educational degree was B.Sc in Industrial and Production Engineering from Bangladesh University of Engineering and Technology. I like to explore new things and find the best and most innovative solutions in every situation.

2 Comments
  1. 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.

Leave a reply

ExcelDemy
Logo