Excel VBA FileDialog
In VBA fieldialog is a property which is used to represent different instances, in filedialog there are four different types of constants which are known as msofiledialogfilepicker which is used to select a file from a given path second one is msofiledialogfolderpicker which name suggests is used to pick a folder and third is msofiledialog open to open a file and the last is msofiledialogsaveas which is used to save a file as a new file.
There are certain circumstances where we want the data from any other file or any other worksheet, and as VBA is used to automate our work, we can open different other files using VBA, and it is done by using the File dialog, the cool part of using this method is that we do not need to provide the path to the code instead we ask the user to browse the file.
As part of the VBA project, we may require to open other workbooks and execute some kind of task with them. In one of the earlier articles, “VBA Workbook.Open,” we have shown how to open files with a specified path and specific file. There, we needed to supply the folder path and file name with its extension. But what if every time a user has to select the different files from the different folders. This is where the “FileDialog” option comes into the picture.
How does VBA FileDialog Option Works?
“If you don’t know the exact path, FileDialog will find and select you the file.” Instead of mentioning the path address and file name, we can present a file open dialog window separately to select the file from any folder of the computer.
“FileDialog” is an object in VBA. To use this option first, we need to define the variable as FileDialog.
Once the variable is declared as “FileDialog,” it is an object variable. To start using this, we need to set the object by using the Application.FileDialog.
As we can see in the above picture, FileDialog has four options with it.
- msoFileDialogFilePicker: This option open the file picker window in front of the user to select the desired file as per their wish.
- msoFileDialogFolderPicker: This option open the dialogue box or window in front of the user to select the Folder.
- msoFileDialogOpen: This will allow the user to open the selected file from the folder.
- msoFileDialogSaveAs: This will allow the user to save the file as a different copy.
As of now, I have selected the option of msoFileDialogFilePicker.
Now we need to design the dialog box which appears in front of us.
Using With Statement, we can design the dialog box.
Inside the with the statement, put a dot to see the IntelliSense list of the properties and methods of the FileDialog option.
To see only the excel files when the file dialog box opens, we need to first remove any of the filters applied.
Now we need to apply a new filter as “Excel Files” with wildcard extension of the excel filesExtension Of The Excel FilesExcel extensions represent the file format. It helps the user to save different types of excel files in various formats. For instance, .xlsx is used for simple data, and XLSM is used to store the VBA code..
Now let’s change the title of the file dialog box.
We can allow the user to select only one file at a time, or we can also allow them to select multiple files as well. For this, we need to use “Allow Multi-Select.”
We have two options here. If TRUE is selected, it will allow the user to select multiple files. If it is, the FALSE user can select only one file at a time.
Another thing we can design with FileDialog is we can actually insist on what should be the default folder when the file dialog box appears. For this use, Initial File Name.
For this, we need to mention the default folder to be opened address path.
Now finally, we need to apply the “Show” method to see the file dialog box.
Sub DoEvents_Example1() Dim Myfile As FileDialog Set Myfile = Application.FileDialog(msoFileDialogFilePicker) With Myfile .Filters.Clear .Filters.Add "Excel Files", "*.xlsx?", 1 .Title = "Choose Your Excel File!!!" .AllowMultiSelect = False .InitialFileName = "D:Excel Files" .Show End With End Sub
Now run the VBA codeVBA CodeVBA code refers to a set of instructions written by the user in the Visual Basic Applications programming language on a Visual Basic Editor (VBE) to perform a specific task. to see the result.
As we can see in the above image file dialog box has opened the mentioned folder by default.
Now we can select any subfolder and select the excel files.
Look at the above image because we have applied the filter parameter as “Excel Files” only.
This will just select the file from the mentioned folder. To store the full path, we need to declare one more variable as a string.
Now inside the with the statement, select “SelectedItems.” Assign the selected items folder path to the newly defined variable.
Now finally show the selected folder path in the VBA message boxVBA Message BoxVBA MsgBox function is an output function which displays the generalized message provided by the developer. This statement has no arguments and the personalized messages in this function are written under the double quotes while for the values the variable reference is provided..
Now I will run the program to see the dialogue box.
Now I have selected the File Name as “1. Charts.xlsx” in the subfolder “Charts.” If I click on OK, we can see the full folder path in the message box.
So, like this, we can use the FileDialog option to select the files from the folder in Excel. Use the below code to select the files.
Sub DoEvents_Example1() Dim Myfile As FileDialog Set Myfile = Application.FileDialog(msoFileDialogFilePicker) Dim FileAddress As String With Myfile .Filters.Clear .Filters.Add "Excel Files", "*.xlsx?", 1 .Title = "Choose Your Excel File!!!" .AllowMultiSelect = False .InitialFileName = "D:Excel Files" .Show FileAddress = .SelectedItems(1) End With MsgBox FileAddress End Sub
This has been a guide to VBA FileDialog. Here we will discuss how to open a file dialog box using VBA code along with an example & downloadable excel template. Below are some useful excel articles related to VBA –