How to open a File Dialog box in Excel using VBA

.FileDialog Syntax

Application.FileDialog(fileDialogType)

The .FileDialog() method takes a parameter in the form of fileDialogType. You can choose the type from a list of predefined values (or constants). These are,

1) msoFileDialogFilePicker: Opens a dialog box to select files.
2) msoFileDialogFolderPicker: Opens a dialog box to select folders.
3) msoFileDialogOpen: Allows users to open a file.
4) msoFileDialogSaveAs: Opens a dialog box as Save as that allows users to save a file.

Now let’s see how we can use it in Excel.

You will have first create an instance of the Application object with the .fileDialog() method. Therefore, open the VBA editor in Excel. I am using a button (an ActiveX Button control) to open the file dialog. So, add a button in your worksheet or sheet1.

Open a File Dialog Box in Excel

Here’s the code to open a file dialog box in excel. It also show other properties the object provides, such as

a) the .Filter property to show only specific file types in the folder
b) the .AllowMultiSelect property to either select a single file or multiple files.

Option Explicit

Private Sub CommandButton1_Click()
    ' Create and set the file dialog object.
    Dim fd As Office.FileDialog
    Set fd = Application.FileDialog(msoFileDialogFilePicker)
    
    With fd
        .Filters.Clear      ' Clear all the filters (if applied before).
        
        ' Give the dialog box a title, word for doc or Excel for excel files.
        .Title = "Select a Word File"
        
        ' Apply filter to show only a particular type of files.
        ' For example, *.doc? to show only word files or
        ' *.xlsx? to show only excel files.
        .Filters.Add "Word Files", "*.doc?", 1
        
        ' Do not allow users to select more than one file.
        ' Set the value as "True" to select multiple files.
        .AllowMultiSelect = False
    
        ' Show the file.
        If .Show = True Then
            Debug.Print .SelectedItems(1)           ' Get the complete file path.
            Debug.Print Dir(.SelectedItems(1))      ' Get the file name.
        End If
    End With
End Sub

In the above example, I have set the value as False for .AllowMultiSelect property, which means you cannot select more than one file. However, if you set the value as True it will allow you to select multiple files and to read all selected file, you can simply run a loop like this,

.AllowMultiSelect = True

If .Show = True Then
     Dim i
     For i = 1 To .SelectedItems.Count
        Debug.Print .SelectedItems(i)
     Next i
End If

Once you have access to the file, you can open it, read its contents or even manipulate its contents. I have explained it here.

Similarly, you can select .xls or .xlsx files (all Excel format) using the .FileDialog() method and view its contents. For example,

Option Explicit

Private Sub CommandButton1_Click()
    ' Create and set the file dialog object.
    Dim fd As Office.FileDialog
    Set fd = Application.FileDialog(msoFileDialogFilePicker)
    
    With fd
        .Filters.Clear
        .Title = "Select an Excel File"
        .Filters.Add "Excel Files", "*.xlsx?", 1
        .AllowMultiSelect = False
        
        Dim sFile As String
    
        If .Show = True Then
            sFile = .SelectedItems(1)
        End If
    End With
    
    If sFile <> "" Then
        Workbooks. Open sFile    ' Open the Excel file.
    End If
End Sub

Open the File Dialog in a Specific Folder

You can tell the FileDialog method to open the dialog box in a specific folder. Usually, the file dialog opens in a default folder such as the Documents folder. However, if you know where your files are, then you can provide the folder name with its full path to the method. For example, if I want to open a folder in D drive, I’ll do this,

Dim fd As Office.FileDialog
Set fd = Application.FileDialog(msoFileDialogFilePicker)
    
With fd
    .Filters.Clear
    .Title = "Select an Excel File"
    .Filters.Add "Excel Files", "*.xlsx?", 1
    .AllowMultiSelect = False

    .InitialFileName = "d:\movies"
End With

Simply provide the path of the folder to the .IntialFileName property. That's it.

Thanks for reading. ☺

← PreviousNext →