VBA FileDialog

In VBA, FileDialog is a property used to represent different instances. For example, in FileDialog, there are four different types of constants: msofiledialogfilepicker, which one can use to select a file from a given path. The second one is msofiledialogfolderpicker which the name suggests, one can pick a folder, and the third is msofiledialog open to open a file. The last is msofiledialogsaveas which one can use to save a file as a new file.

We want the data from any other file or worksheet in certain circumstances. As VBA is used to automate our work, we can open different other files using VBA. One can do the same 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 must open other workbooks and execute some tasks. For example, in one of the earlier articles, “VBA Workbook.Open,” we have shown how to open files with a specified path and specific file. Therefore, we needed to supply the folder path and file name with its extension. But what if a user has to select the different files from the different folders every time? This situation is where the "FileDialog" option comes into the picture.

If you do not know the exact path, the FileDialog will find and select 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 computer folder.

The "FileDialog" is an object in VBA. So, to use this option first, we need to define the variable as FileDialog.

Once we declare the variable as "FileDialog," it is an object variable. To start using this, we need to set the object using the Application.FileDialog.

As we can see in the above picture, the 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 option 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 in front of us.

Using the With Statement, we can design the dialog box.

Inside the With statement, we must put a dot to see the IntelliSense list of the properties and methods of the FileDialog option.

We need to remove any filters applied first to see only the Excel files when the file dialog box opens.

Now, we need to apply a new filter as "Excel Files" with a wildcard extension of the excel files.

Now, let us 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. 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. We can insist on what should be the default folder when the file dialog box appears. For this use, InitialFileName.

We need to mention the default folder to open the address path.

Finally, we must apply the "Show" method to see the file dialog box.

Code:

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

As we can see in the above image, the 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.

It will just select the file from the mentioned folder. We must declare one more variable as a string to store the full path.

Now, inside the With statement, select "SelectedItems." Next, assign the selected items folder path to the newly defined variable.

Finally, show the selected folder path in the VBA message box.

Now, we will run the program to see the dialog box.

Now, we have selected the "FileName" as "1. Charts.xlsx" in the subfolder "Charts." If we click "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. Then, use the below code to select the files.

Code:

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