Rafiul Haq
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.
- Next, type the following code in the Module window.
Option Explicit
Sub Open_Default_File_Dialog_1()
Application.FileDialog(msoFileDialogOpen).Show
End Sub
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.
- By doing so, the File Open dialog box will pop up.
- Thus, we open the file dialog default folder in Excel VBA.
Read More: How to Open Workbook with Variable Name with Excel VBA
Similar Readings
- How to Open Another Workbook and Copy Data with Excel VBA
- [Fixed!] Method Open of Object Workbooks Failed (4 Solutions)
- Excel VBA to Open Workbook in Background (2 Useful Examples)
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
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.
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 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.
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!
