How to Open File Dialog Default Folder with Excel VBA (3 Ways)

VBA can automate many mundane tasks in Excel. It can save a lot of time. Knowing the default folder location allows us to be certain of where our file will be saved. Moreover, we can change  this to any folder location. In this article we will show you how to open the file dialog default folder in Excel using three VBA Macros.


Download Practice Workbook


3 Handy Approaches to Open File Dialog Default Folder with Excel VBA

We will demonstrate how to use the first two techniques to open the file dialog default folder. Then, we will show you the steps to set and open the file dialog default folder in the last method.


1. Using VBA FileDialog Property to Open File Dialog Default Folder

For the first method, we will be using the VBA Show method along side with the FileDialog property to open the file dialog with the default folder in Excel. Here, we will be using the msoFileDialogOpen parameter inside the FileDialog property to open the default file window.

Steps:

  • To begin with, press ALT+F11 to bring up the VBA Module window. Alternatively, you can do this from the Developer tab →  select Visual Basic.
  • Then, from the Insert tab → select Module. We will type the VBA code here.

Excel VBA Open File Dialog Default Folder

  • Next, type the following code in the Module window.
Option Explicit
Sub Open_Default_File_Dialog_1()
Application.FileDialog(msoFileDialogOpen).Show
End Sub

Excel VBA Open File Dialog Default Folder 2

VBA Code Breakdown

  • Firstly, the Sub procedure is called “Open_Default_File_Dialog_1“.
  • Secondly, we use the msoFileDialogOpen data type inside the FileDialog property.
  • Finally, using the Show method, we open the default file dialog folder.
  • After that, we will execute the code.
  • So, Save the Module and then put the cursor inside the code and press Run.

Excel VBA Open File Dialog Default Folder 3

  • By doing so, the File Open dialog box will pop up.
  • Thus, we open the file dialog default folder in Excel VBA.

Excel VBA Open File Dialog Default Folder 4

Read More: How to Open Workbook with Variable Name with Excel VBA


Similar Readings


2. Applying VBA GetOpenFilename Method

In this second method, we will use the VBA GetOpenFilename method to open the file dialog default folder window in Excel. Moreover, we will specify the file format inside the file dialog box. Additionally, we will allow multiple file selection in this VBA code.

Steps:

  • Firstly, as shown in the first method, bring up the Module window.
  • Secondly, type the following code.
Option Explicit
Sub Open_Default_File_Dialog_2()
Dim Show_File_Dialog As String
Show_File_Dialog = Application.GetOpenFilename(FileFilter:= _
"Excel files (*.xlsx*), *.xlsx*", _
Title:="Select an Excel File", MultiSelect:=True)
End Sub

Excel VBA Open File Dialog Default Folder 5

VBA Code Breakdown

  • To begin with, the Sub procedure is called “Open_Default_File_Dialog_2“.
  • Then, we define the variable type.
  • After that, we use the GetOpenFilename method to show the default folder window.
  • Moreover, it has several parameters. Firstly, we are setting the file type as xlsx. Secondly, we set the title of the window. Finally, we are allowing multiple files to be selected.
  • Thus, this code works.
  • After that, as shown in the first method, Run this code.
  • Then, the file dialog default folder will appear.

Output of Method 2

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


3. Setting and Opening Default File Dialog Folder with Excel VBA

In this last method, 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 open that folder. Moreover, we will specify the file format as xlsx here similar to the second method, however, we will set the multiple file selection to false this time.

Steps:

  • Firstly, as shown in the first method, bring up the Module window.
  • Secondly, type the following lines of code.
Option Explicit
Sub Set_Default_File_Dialog_Folder()
Dim File_Dialog_Box As Office.FileDialog
Set File_Dialog_Box = Application.FileDialog(msoFileDialogFilePicker)
    With File_Dialog_Box
        .Filters.Clear
        .Filters.Add "Excel Files", "*.xlsx?", 1
        .Title = "Select an Excel File"
        .AllowMultiSelect = False
        .InitialFileName = "C:\Users\Rafiul Haq\Desktop\Exceldemy\16\"
        .Show
    End With
End Sub

VBA Code 3

VBA Code Breakdown

  • To begin with, the Sub procedure is called “Set_Default_File_Dialog_Folder“.
  • Then, we define the variable type.
  • Next, we set the variable as the “Application.FileDialog” property and the data type as “msoFileDialogFilePicker”. This will ask the user to select a file.
  • Afterward, inside the With statement we set the default file dialog folder inside the InitialFileName property.
  • Lastly, using the Show method, it will open the changed default folder.
  • Thus, this code works.
  • After that, as shown in the first method, Run this code.
  • Then, we will see that the default folder location has changed.

Output of Method 3

Read More: How to Browse for File Path Using Excel VBA (3 Examples)


Conclusion

We have shown you three quick ways to open the file dialog default folder in Excel using VBA Macros. If you face any problems regarding these methods or have any feedback for me, feel free to comment below. Moreover, you can visit our site ExcelDemy for more Excel-related articles. Thanks for reading, keep excelling!


Related Articles

Rafiul Haq

Rafiul Haq

Hello! This is Rafiul. I have an engineering degree and an MBA (finance) degree. I am passionate about all things related to data, and MS Excel is my favorite application. I want to make people's lives easier by writing easy-to-follow and in-depth Excel and finance related guides here at ExcelDemy.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo