Have you ever wished you could develop a simple way for users to select files to open from within Excel? Perhaps you want to impose a level of control over which file path users select the file(s) from.

This can be done by using the Excel VBA Applicaton.FileDialog object. In fact, I myself have used this on several occasions when customizing reports. For instance, the following figure illustrates just how much you can customize and control reports using the FileDialog object.

Example of how you can use Application.FileDialog

This makes for a great method in guiding the user in a user friendly fashion and can tend to simplify the overall user experience. At the same time, it provides the owner or administrator of the report a greater level of control and confidence in the integrity of the report or file.

The Excel VBA Application.FileDialog object is useful code for VBA developers that represents an instance of the file dialog.

The syntax is pretty simple:

expression.FileDialog(fileDialogType)

where expression can be an Application object or a variable representing an Application object.

The FileDialog object requires one parameter, fileDialogType which simply represents the type of file dialog. The fileDialogType data type is msoFileDialogType. Note the following list of msoFileDialogType constants available for the fileDialogType parameter.

1. msoFileDialogFilePicker – This constant allows users to select a file.
2. msoFileDialogFolderPicker – This constant allows users to select a folder.
3. msoFileDialogOpen – This constant allows users to open a file.
4. msoFileDialogSaveAs – This constant allows users to save a file.

Again, these are simply representative of the four different file dialog boxes available from the Excel VBA FileDialog object. So let’s take a look at each of the different dialog boxes available using the VBA Application.FileDialog property.

Using msoFileDialogPicker

First, let’s look at a simple example using msoFileDialogPicker. Press Alt + F11 to open the VBA Editor in Excel, or you can simply right click on your open worksheet tab and select ‘View Code’.

Open the VBA editor

Either method will work fine. Now let’s right-click on ‘Microsoft Excel Objects’ in the Project Explorer and select Insert Module.

Insert a new module

Let’s name our new subroutine ‘GetFileDialog’ and type in the following code:

Sub GetFileDialog()
With Application.FileDialog(msoFileDialogFilePicker)
.Show
End With
End Sub

Now press F5 to run the macro. This should initialize the File Picker dialog box.


The file picker dialog box
This is great. The VBA Application.FileDialog property in our code works as expected. However, this is only part of a solution. At this point, we have not added any code that allows the user to actually open a file so our code isn’t quite relevant just yet.
We now need to add a few lines of extra code to make our File Dialog box relevant. There’s no point in programming a File Dialog box to appear if it won’t allow for opening a file. So now we will add some new lines of code to our existing sub routine and rename it.
Sub GetFile()
Dim SelectedItem As String
With Application.FileDialog(msoFileDialogFilePicker)
If .Show = -1 Then
'if user clicks OK
SelectedItem = .SelectedItems(1)
MsgBox "You have selected: " & SelectedItem
Else
'if user clicks Cancel
End If
End With
End Sub

In the previous figure, the code now includes a declared variable (Dim SelectedItem As String) that becomes the selected file and path from the File Dialog. If, after the file is selected, the user clicks ‘OK’ a message box appears telling the user that “You have selected: <file path>”.
Select the file, then click OK
The message box written into our code will generate the following when you click on ‘OK’ in the File Dialog box.
The program returns the expected file
Restricting the File Dialog box to a single file selection
One major issue with our current code in the previous example is that a user could still make multiple selections. Notice in the following figure what happens if the user holds the Ctrl key down while clicking on multiple file selections.
Multiple selection for filedialog


 In order to restrict this from happening, we need to add another line of code: the FileDialog.AllowMultiSelect property. We will simply set it equal to ‘False’.
There are also a couple of other methods for customizing the FileDialog object. First, we can change the title of the File Dialog box from the default ‘Browse’ to something like, ‘Select a file’ by using the FileDialog.Title property. We can also customize the ‘OK’ button by using the FileDialog.ButtonName property.
Sub GetFileCustom()
Dim SelectedItem As String
With Application.FileDialog(msoFileDialogFilePicker)
.AllowMultiSelect = False
.Title = "Select a file"
.ButtonName = "Select"
If .Show = -1 Then
'if user clicks OK
SelectedItem = .SelectedItems(1)
MsgBox "You have selected: " &amp;amp;amp;amp; SelectedItem
Else
'if user clicks Cancel
End If
End With
End Sub
Note the File Dialog box title as well as the ‘OK’ button titles have been changed according to our customizations using the various FileDialog object properties.
Now, only a single “select” is allowed
Using msoFolderDialogPicker
The Excel VBA Applicaton.FileDialog object also offers a FileDialog type for opening a folder. The msoFolderDialogPicker constant works much the same way as the msoFileDialogPicker but obviously for opening a folder rather than a file.
We can simply use the code of our previous sub routine as a template for a new sub routine using the msoFileDialogPicker constant. Note that we have change the name of our sub routine and our variable name, as well as a few other details.
Sub GetFolder()
Dim SelectedFolderItem As String
With Application.FileDialog(msoFileDialogFolderPicker)
.Title = "Select folder"
.ButtonName = "Select"
.InitialFileName = "C:\"
If .Show = -1 Then
'if user clicks OK
SelectedFolderItem = .SelectedItems(1)
MsgBox "You have selected: " &amp;amp;amp;amp; SelectedFolderItem
Else
'if user clicks Cancel
End If
End With
End Sub
Note that the other big changes here are the FileDialog data type constant (msoFileDialogFolderPicker) and the custom title (“Select folder”). We have also added the use of the FileDialog.InitialFileName property.
We did not use this in the previous example. However, we could have. This property simply allows you to set the file path default like we have done here with “C:\”.
Set default file path with msoFileDialogFolderPicker
Using msoFileDialogOpen
The msoFileDialogOpen data type allows you to open a file using the FileDialog object. In the following example we will now declare a variable fDialog and set it to our Application.FileDialog object to clean up our code a bit.
Sub OpenFile()
Dim SelectedFileItem As String
Dim fDialog As FileDialog
Set fDialog = Application.FileDialog(msoFileDialogOpen)
With fDialog
'Custom properties
.Title = "Select a file"
.InitialFileName = "C:\"
'Filters for file types allowed
.Filters.Clear
.Filters.Add "Excel files", "*.xlsx"
If .Show = -1 Then
'if user clicks OK
SelectedFileItem = .SelectedItems(1)
MsgBox "You have selected: " &amp;amp;amp;amp; SelectedFileItem
Workbooks.Open (SelectedFileItem)
Else
'if user clicks Cancel
End If
End With
End Sub
Also note the use of the FileDialog.Filters property in order to control which type of files (Excel files including .xlsx) can be opened. Bear in mind that the msoFileDialogOpen constant does not actually open the selected file. That requires adding in the Workbooks.Open method.
Using msoFileDialogSaveAs
This constant is just as straightforward as the others. However, it is obviously different in the sense that it is the only type that is for saving a file rather that opening a file, multiple files, or a folder. Much like the first example, we simply need to use the FileDialog.Show method.
Sub SaveFileAs()
Dim fDialog As FileDialog
Set fDialog = Application.FileDialog(msoFileDialogSaveAs)
With fDialog
.Show
End With
End Sub
Run this macro by pressing F5, and you will get the following File Dialog box:
msoFileDialogSaveAs example
There you have it. A quick introduction and run down of the Excel VBA Application.FileDialog object and the four different type constants that are available.  Each of the code snippets illustrated here can be found in the example file here: Filedialog example file
Tagged with: Excel, FIle, VBA