VBA FILEDIALOG

What is Excel VBA FileDialog?

In Excel VBA, the FileDialog can show different instances of a file dialog box, which gains access to the File Explorer in the Windows files as shown.

The Application.FileDialog property returns a FileDialog object representing an instance of the file dialog. The FileDialog property takes a single argument, MsoFileDialogType, that determines the type of FileDialog object that the property returns. The MsoFileDialogType has multiple different types you can use for various reasons.

There are four types of FileDialog object:

msoFileDialogFilePicker: Allows the user to select a file.
msoFileDialogFolderPicker: Allows the user to select a folder.
msoFileDialogOpen: Allows the user to open a file.
msoFileDialogSaveAs: Allows the user to save a file.

To display a file dialog box using the FileDialog object, you must use the VBA FileDialog Show method. After a dialog box is displayed, no code executes until the user dismisses the dialog box. The selected file or folder can be accessed using VBA FileDialog SelectedItem.

This subroutine opens a file dialog in your system from which you can select any file.

After selecting your preferred file, a Message box appears.

What is Excel VBA FileDialog?

VBA FileDialog is a property that allows you to open, select, and save files and folders in VBA.
There are four types of FileDialogs available in VBA: msoFileDialogFilePicker, msoFileDialogFolderPicker, msoFileDialogOpen, and msoFileDialogSaveAs.
You can customize the FileDialog by setting properties such as title, initial file name, and filters.
To retrieve the selected file(s) from a FileDialog in VBA, you can use the SelectedItems property of the FileDialog object.
You can also select multiple files using VBA FileDialogs by setting the AllowMultiSelect property of the FileDialog object to True.

How to Use VBA FileDialog Option?

Learn how to use VBA FileDialog by following the steps below:

In the workbook, click “Developer” and then go to “Visual Basic.”

It opens the VBA Editor, where you click the “Insert” button and select the “Module” option.

Start with creating a simple subroutine that lets you select multiple files in FileDialog.
Write the name of the subroutine.
Define a VBA FileDialog datatype and a Variant datatype.

Note: Declare VBA FileDialog SelectedItem as a Variant data type. Any other data type will throw an error.
Call the VBA FileDialog function and select the VBA FileDialog filter object ‘msoFileDialogFilePicker’.

It enables you to select any files you want in the file dialog.
To select multiple files in FileDialog, set the AllowMultiSelect functionality as “True.”

Now, you can select multiple files in FileDialog.
Initialize an If-Else statement to check if the VBA FileDialog Show option is equal to “-1.”

Here, we check whether the Show function returns ‘-1’ since it depicts the “Open” button in the Excel VBA FileDialog option.
If it returns ‘-1’, print their directories stored in the Variant variable in a message box.

The For loop is initialized to select multiple files.

Code:

Sub SelectMultipleFiles()
Dim fDialog As FileDialog
Dim vrtSelectedItem As Variant
Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
fDialog.AllowMultiSelect = True
If fDialog.Show = -1 Then
For Each vrtSelectedItem In fDialog.SelectedItems
MsgBox vrtSelectedItem
Next vrtSelectedItem
End If
End Sub
Run the VBA FileDialog code by clicking the green arrow button in the VBA Toolbar.

Let’s see some interesting examples below to learn how to use VBA FileDialog Filter accurately.

Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)

If you want to learn Excel and VBA professionally, then Excel VBA All in One Courses Bundle (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

Start Learning Now

Examples

Learn how to use VBA FileDialog reference with the following examples.

Example #1

For example, you need a program where, once you select a file, it will only return the file name and not the directory name as usual. It can be done with the VBA FileDialog Filter option.

Step 1: Start with creating a function to extract the file name as a String value.

It returns a string value.

Step 2: Get an integer variable to find the last backslash in the directory.

Step 3: Get the file name by selecting the string to the right of the last backslash, and then ignoring the other values by subtracting the position of the Last backslash from the size of the string.

Then, end the function and continue to the subroutine.

Step 4: Name the subroutine used to display only the name.

Step 5: Declare VBA variables for the FileDialog, the VBA FileDialog SelectedItems, Variant for the file directories, and a String to store the name of the file.

Step 6: Open the FileDialog view. Enable viewing multiple files.

Step 7: Initialize an If statement to check if the File Dialog box is open.

If it is open, assign the items selected by the user to the VBA FileDialog SelectedItems variable.

Step 8: Initialize a FOR-loop checking for the file directories. Call the function defined earlier to extract the file name and store it in the string variable.

Print the file name in a messagebox.

Step 9: Set an Else condition if the user closes the FileDialog without selecting any files. Then empty the FileDialog variable to save resources.

Code:

Sub ProcessSelectedFiles()
Dim FileDialog As FileDialog
Dim SelectedFiles As FileDialogSelectedItems
Dim FilePath As Variant
Dim FileName As String
Set FileDialog = Application.FileDialog(msoFileDialogOpen)
FileDialog.AllowMultiSelect = True
If FileDialog.Show = -1 Then
Set SelectedFiles = FileDialog.SelectedItems
For Each FilePath In SelectedFiles
FileName = GetFileNameFromPath(FilePath)
MsgBox “Selected File: ” & FileName
Next FilePath
Else
MsgBox “No files selected.”
End If
Set FileDialog = Nothing
End Sub

Function GetFileNameFromPath(ByVal FilePath As String) As String
Dim LastBackslash As Integer
LastBackslash = InStrRev(FilePath, “\”)
GetFileNameFromPath = Right(FilePath, Len(FilePath) – LastBackslash)
End Function

Step 10: Run the code to view the files.

VBA FileDialog in Excel - Example 1 - Step 10a

VBA FileDialog in Excel - Example 1 - Step 10b

Example #2

Ever felt annoyed while referring to values corresponding to Excel values, when you will need to open a new Window and see the data yourself? You can instead write a sub-procedure to open a file and display its contents to be able to see.

The file in question is:

Step 1: Name a subroutine open and display file.

Step 2: Define two variables, one to open the FileDialog and the other is to store the Selected File’s directory.

Step 3: Open the FileDialog in your system with this command.

Step 4: Check if the FileDialog is open by checking if it is “-1” and then assign the item you’ve selected. VBA FileDialog SelectedItem is depicted as an array with a singular element.

Step 5: Perform error handling.

Step 6: Open the selected file and assign it as “#1” that is, file number 1.

Step 7: Check if there is any error in opening the files. If not, open it and copy the file contents into a VBA variable, FileContent.

After storing the input, close the file to prevent resources from being drained in the background.

Step 8: Define the “Else” part of the earlier step.

Step 9: Reset the Error parameters.

Step 10: Print the contents of the file using a Message box VBA function.

Step 11: If the FileDialog is closed without selecting any files, print the response in a Message Box to prevent any errors.

Step 12: Empty the FileDialog variable to save resources.

Code:

Sub OpenAndDisplayFile()
Dim FileDialog As FileDialog
Dim SelectedFile As Variant
Set FileDialog = Application.FileDialog(msoFileDialogOpen)
If FileDialog.Show = -1 Then
SelectedFile = FileDialog.SelectedItems(1)
On Error Resume Next
Open SelectedFile For Input As #1
If Err.Number = 0 Then
FileContent = Input$(LOF(1), #1)
Close #1
Else
FileContent = “Error opening file.”
End If
On Error GoTo 0
MsgBox “File Content:” & vbNewLine & FileContent
Else
MsgBox “No file selected.”
End If
Set FileDialog = Nothing
End Sub

Step 13: Click the green arrow button when you want to run the code. It will pop up a Message Box once run.

Excel VBA FileDialog - Example 2 - Step 13a

Select a file and double-click it. It prints a message box as shown below.

Excel VBA FileDialog - Example 2 - Step 13b

Important Things to Note

Always include error handling in your VBA code to handle situations where the user cancels the dialog or encounters errors.
Before processing the selected files or folders, check if the results are null or empty to handle cases where the user cancels the dialog without selecting.
Choose the appropriate type of FileDialog (e.g., Open File, Save File, Folder Picker) for your specific task.
If your code allows multiple file selections, be prepared to handle multiple items in the SelectedItems collection.
Use proper path manipulation functions to work with selected file and folder paths. The FileSystemObject can be helpful for this purpose.
Release any resources (e.g., FileDialog objects) explicitly when you’re done using them to free up system resources.

Frequently Asked Questions (FAQs)

1. What types of FileDialogs are available in VBA?

There are four types of VBA FileDialog Reference:

• msoFileDialogFilePicker: Allows the user to select one or more files.
• msoFileDialogFolderPicker: Allows the user to select a folder.
• msoFileDialogOpen: Allows the user to select one or more files that can be opened in the host application.
• msoFileDialogSaveAs: Allows the user to select a single file that can be saved as the current file.

2. How can I customize the FileDialog in VBA?

To customize the FileDialog in VBA, you can set various properties such as title, initial file name, and filters. Some of the properties to be set are:

• AllowMultiSelect: Allows the user to select multiple files.
• ButtonName: Text displayed on the action button of a file dialog box.
• DialogType: Change the MsoFileDialogType.
• Filter: Set a file filter to filter file types the user can select.
• InitialFileName: The initial path to be opened.
• InitialView: The initial file view.
• Title: Title of the Open file dialog window.

3. How do I retrieve the selected file(s) from a FileDialog in VBA?

• To retrieve the selected file(s) from a FileDialog in VBA, you can use the SelectedItems property of the FileDialog object.
• This property returns a FileDialogSelectedItems collection that contains a list of the paths of the files that the user selected from the file dialog box.
• You can loop through the SelectedItems collection to process each selected file.
• Once you have the path of the selected file(s), you can use it in your VBA code to perform any action on the file(s).

4. Is it possible to select multiple files using VBA FileDialogs?

Yes, it is possible to select multiple files using VBA FileDialogs. Set the AllowMultiSelect property of the FileDialog object to True.

VBA FILEDIALOG