VBA FileDialog

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.

VBA FileDialog

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.

You can download this VBA FileDialog Excel Template here – VBA FileDialog Excel Template

“FileDialog” is an object in VBA. To use this option first, we need to define the variable as FileDialog.

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

VBA FileDialog applictaion

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.

VBA FileDialog Example 1

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

VBA FileDialog Example 1-1

To see only the excel files when the file dialog box opens, we need to first remove any of the filters applied.

VBA FileDialog Example 1-2

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

Example 1-3

Now let’s change the title of the file dialog box.

VBA FileDialog Example 1-4

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

VBA FileDialog Example 1-5

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.

VBA FileDialog Example 1-6

For this, we need to mention the default folder to be opened address path.

Example

Now finally, we need to 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

Example

Now run the VBA code to see the result.

VBA FileDialog Example 1-9

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.

Example 1-10

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.

Example 1-11

Now inside the with the statement, select “SelectedItems.” Assign the selected items folder path to the newly defined variable.

VBA FileDialog Example 1-12

Now finally show the selected folder path in the VBA message box.

VBA FileDialog Example 1-13

Now I will run the program to see the dialogue box.

Example 1-14

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.

VBA FileDialog Example 1-15

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.

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

Recommended Articles

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 –

  • 3 Courses
  • 12 Hands-on Projects
  • 43+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>