If you read the article about the FileDialog object in Excel here you will already know a bit about the msoFileDialogOpen constant available as a parameter for the FileDialog object. We are going to take a deeper dive into the msoFileDialogOpen constant in this article.

WHY WOULD YOU WANT TO LEARN HOW TO USE THE FILE OPEN DIALOG TYPE?

If you have ever wanted to develop a way for users of your Excel files to open files from within your file, this is the Excel VBA open workbook method you need. The file open dialog type is pretty simple VBA to get started and there are many great options to tailor it to your needs.  Furthermore, once you get comfortable with the VBA for the file open dialog type, you can use this code in your own custom user forms to further customize the user experience.

Another great reason to use the Excel VBA file open dialog is that it allows you a bit of control over the process that involves opening a file or files. The file open dialog type even allows you to set the file path so the user doesn’t have to spend extra time navigating to a folder. But more on this a bit later.

WHAT IS THE FILE OPEN DIALOG?

When we say file open dialog, we are actually referring to one of four dialog types that can be used with the FileDialog object in Excel VBA. If you are unfamiliar with the FileDialog object, take a few minutes to read an introduction to it here.

The actual code for the file open dialog constant is msoFileDialogOpen. It is a parameter used with the FileDialog object. It simply tells the FileDialog object in our VBA code what we want to do in order for it to generate the proper file dialog.

The file dialog does not open the file. It simply takes the information from the user about the file to be opened. We will need to supply different VBA code to actually execute the opening of the file and we will get to that a bit later on.

But enough of the talk. Let’s get to some VBA code and see the Excel VBA file open dialog in action.

HOW TO SHOW THE FILE OPEN DIALOG

The first thing we need to do is open our Visual Basic editor in Excel by pressing Alt + F11. Once the editor is open, right-click on Microsoft Excel Objects under VBAProject for your file.

Then go to Insert and select Module.

Now you have a place to copy and paste or type the following code into.

Sub openFileDialog()

With Application.FileDialog(msoFileDialogOpen)
.Show
.AllowMultiSelect = False
End With

End Sub
We start by naming our subroutine. We use a With statement to allow us to keep our code less redundant. This basically allows us to type our object and parameter once and add properties after a dot in subsequent lines. So we set our dialog with Application.FileDialog(msoFileDialogOpen).
Then we code two properties for our file dialog. The first property is Show. This will make the file dialog appear. Then we set the AllowMultiSelect property to False. This property can be set to either True or False and False will only allow one file to be selected.
Press F5 to run the macro. You should see something like the following appear.

Note that if you hold Ctrl down and try to select more than one file, you cannot highlight more than one.
HOW TO OPEN MULTIPLE FILES AND SET FILE PATH
Now let’s build upon what you just did. Let’s now change the AllowMultiSelect value to TRUE so users can select multiple files. Let’s also set the file path by using the InitialFileName property. Now go back to your VBA editor and add the following code for our new sub routine.
Sub openFilesDialog()

    With Application.FileDialog(msoFileDialogOpen)
    .Show
    'Set the file path
    .InitialFileName = "C:\Users\Public"
    'allow the user to select more than one file
    .AllowMultiSelect = True
    End With

End Sub
Run the macro by pressing F5 and notice that you can now highlight multiple files by holding down Ctrl while you click on them.  Also note that the file dialog opens to the filepath we pre-selected with the InitialFileName property.

Up to this point, you can now code a file dialog to allow users to select single and multiple files for opening and you can also set the filepath to choose the files from. That’s a pretty good foundations. Now you should be ready to bring this all together and add some more VBA to actually make your file dialog fully functional.
ADDING A DIALOG BOX TITLE AND ACTUALLY OPENING THE FILE
In the final VBA example, we are going to add some really cool elements. First, we will declare a couple of variables. SelectedFileItem will be the variable set to the name of the file we want Excel to open. We will also declare the variable fDialog and set it to our Application.FileDialog object. This will help reduce clutter in the rest of the VBA code.
We also add a couple of new file dialog properties: Title and Filters. The Title property allows us to customize the title at the top of the file dialog. The Filters property allows us to limit the type of file the user can open in the file dialog.
Note also that the .Show method is within an If statement the executes subsequent code based on which button is clicked when the file is selected: Open or Cancel. For more about FileDialog.Show method, click here.
As promised earlier, we also add the necessary VBA code to actually open the selected file: the Workbook.Open method. Here is the final VBA for the subroutine.
Sub openFile()
'This example subroutine uses the Workbooks.Open method to actually open the file selected in the File Open dialog
Dim SelectedFileItem As String
Dim fDialog As FileDialog
Set fDialog = Application.FileDialog(msoFileDialogOpen)

    With fDialog
    'Custom dialog title
    .Title = "Select a file"
    .AllowMultiSelect = False
    'Set the file path
    .InitialFileName = "C:\Users\Public"
    'Filters for file types allowed
    .Filters.Clear
    .Filters.Add "Excel files", "*.xlsx"
    If .Show = -1 Then
    'if user clicks OK
    SelectedFileItem = .SelectedItems(1)
    'Open the selected file
    Workbooks.Open (SelectedFileItem)
    Else
    'if user clicks Cancel
    End If
    End With
End Sub
Simply adapt to your own needs as far as replacing the file path. Then press F5 to run the macro and select the file you want to open. Once you click ‘OK’ you should see the selected file open in Excel.
Now you know how to not only use the Excel VBA file open dialog type, but you also know how to code your macros to actually open that file using the Workbook.Open method. Again, this same concept can be used in custom user forms to further enhance and customize the user experience within Excel for your files and projects.
Tagged with: Excel, FIle, Open, Sample Code, VBA