There may be times when you need to ask the user to select a file to open. This can be done using the open file dialog. Keep in mind that the open file dialogs doesn’t actually open anything. It returns the path of the file or files selected.

You can download the workbook for this article here.

Jump To:

• Example 1
• Example 2
• Custom Title
• Start Folder Path, InitialFilePath
• Filter File Types, Filters

Example 1, Select Single File:

In this example an open file dialog is displayed and the user is asked to select a file to open. The path of the file selected by the user is then printed in cell A2. Note that the open file dialog doesn’t actually open any files, it only returns the path the user has selected:

Sub Example1()
Dim intChoice As Integer
Dim strPath As String

'only allow the user to select one file
Application.FileDialog(msoFileDialogOpen).AllowMultiSelect = False
'make the file dialog visible to the user
intChoice = Application.FileDialog(msoFileDialogOpen).Show
'determine what choice the user made
If intChoice <> 0 Then
    'get the file path selected by the user
    strPath = Application.FileDialog( _
        msoFileDialogOpen).SelectedItems(1)
    'print the file path to sheet 1
    Cells(2, 1) = strPath
End If
End Sub


Result:

After selecting the file “test.txt” from the directory “D:Temp”, the path is printed in cell A2:

The line below tells the program to only allow the user to select one file:

Application.FileDialog(msoFileDialogOpen).AllowMultiSelect = False


The line below makes the open file dialog visible to the user:

intChoice = Application.FileDialog(msoFileDialogOpen).Show

If the user cancels the dialog, intChoice will return “0”. The line below returns the path selected by the user:

strPath = Application.FileDialog( _
    msoFileDialogOpen).SelectedItems(1)


Example 2, Select Multiple Files:

In the example below an open file dialog is opened and asks the user to select files to open. Unlike Example 1 the user is permitted to select multiple files in this example. The path of all the files selected by the user is printed in column A. Note that the open file dialog doesn’t actually open any files, it only returns the path the user has selected:

Sub Example2()
Dim intChoice As Integer
Dim strPath As String
Dim i As Integer

'allow the user to select multiple files
Application.FileDialog(msoFileDialogOpen).AllowMultiSelect = True
'make the file dialog visible to the user
intChoice = Application.FileDialog(msoFileDialogOpen).Show
'determine what choice the user made
If intChoice <> 0 Then
    'get the file path selected by the user
    For i = 1 To Application.FileDialog(msoFileDialogOpen _
        ).SelectedItems.Count
        strPath = Application.FileDialog(msoFileDialogOpen _
        ).SelectedItems(i)
        'print the file path to sheet 1
        Cells(i + 1, 1) = strPath
    Next i
End If


Result:

After selecting the 3 files, “Test1.txt”, “Test2.txt” and “Test3.txt” from the folder “D:Temp” their paths are printed in Column A:

Custom Title:

By default the title of the Open File Dialog is “Open File”:

Using the .Title property you can set a custom title for the dialog. In the example below the title “Random Title For Dialog” will be used:

Sub Example3()
Dim intChoice As Integer

'change the display name of the open file dialog
Application.FileDialog(msoFileDialogOpen).Title = _
    "Random Title For Dialog"
'make the file dialog visible to the user
intChoice = Application.FileDialog(msoFileDialogOpen).Show
'determine what choice the user made
If intChoice <> 0 Then
    'your code here
End If
End Sub


Result:

Start Folder Path, InitialFileName:

Lets say there is a specific folder we would want the dialog to start in. This could be set by the InitialFileName property. In the example below the open file dialog will start in the directory “D:TempFolder to Start“:

Sub Example4()
Dim intChoice As Integer

'Select the start folder
Application.FileDialog(msoFileDialogOpen _
    ).InitialFileName = "D:TempFolder to Start"
'make the file dialog visible to the user
intChoice = Application.FileDialog(msoFileDialogOpen).Show
'determine what choice the user made
If intChoice <> 0 Then
    'your code here
End If
End Sub


Result:

Filter File Types, Filters:

In the example below there are multiple file types in our folder, .txt, .xlsx, .docx:

But when the open file dialog appears, we only want the user to see the .txt files. This can be achieved by applying a filter:

Sub Example5()
Dim intChoice As Integer

'Remove all other filters
Call Application.FileDialog(msoFileDialogOpen).Filters.Clear
'Add a custom filter
Call Application.FileDialog(msoFileDialogOpen).Filters.Add( _
    "Text Files Only", "*.txt")
'make the file dialog visible to the user
intChoice = Application.FileDialog(msoFileDialogOpen).Show
'determine what choice the user made
If intChoice <> 0 Then
    'your code here
End If
End Sub


Result:

The line below removes all other filter options from the filter list:

Call Application.FileDialog(msoFileDialogOpen).Filters.Clear


The line below adds a custom filter. The name of the custom filter is “Text Files Only”. The expressions “*.txt” means that we only want the files that end in “.txt” to appear in the dialog:

Call Application.FileDialog(msoFileDialogOpen _
    ).Filters.Add("Text Files Only", "*.txt")


You can download the workbook for this article here.

See Also:

• Excel VBA, Reading Text Files.
• Find and List All Files and Folders in a Directory

If you need assistance with your code, or you are looking for a VBA programmer to hire feel free to contact me. Also please visit my website  www.software-solutions-online.com