In VBA 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 VBA 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.
Excel VBA FileDialog
As part of the VBA project, we may require to open other workbooks and execute some kind of task with them. In our one of the earlier article “VBA Workbook.Open” we have shown how to open files with specified path and specific file. In that example, 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 file from the different folders. This is where the “VBA FileDialog” option comes into the picture. In this article, we will guide you FileDialog option through Excel VBA.
What Does FileDialog Option Do in Excel VBA?
“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 Application.FileDialog.
As we can see in the above picture VBA 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 statement put a dot to see the intellisense list of the properties and methods of VBA 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 files.
Now let’s change the title of the filedialog 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 VBA FileDialog is we can actually insist what should be default folder when the filedialog 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 code to see the filedialog box.
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 String.
Now inside the with 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 box.
Now I will run the program to see the dialogue box.
Now I have selected the File Name as “1. Charts.xlsx” in the sub folder “Charts”. If I click on OK, we can see the full folder path in the message box.
So, like this, we can use the VBA FileDialog option to select the files from the folder in Excel. Use 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 simple & advanced examples & explanation. Below are some useful excel articles related to VBA –