Excel VBA Open File Dialog

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, 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:

Excel VBA Open File Dialog Example 1
After selecting the file “test.txt” from the directory “D:Temp”, the path is printed in cell A2:

Excel VBA Example 1 Result
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:

Excel VBA Open File Dialog Example 2 Select Multiple Files
After selecting the 3 files, “Test1.txt”, “Test2.txt” and “Test3.txt” from the folder “D:Temp” their paths are printed in Column A:

Excel VBA Example 2 Result


Custom Title:

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

Excel VBA Open File Dialog Title Default

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:

Excel VBA Open File Dialog, Custom Title


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 directoryD: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:

Excel VBA Open File Dialog, Start Folder


Filter File Types, Filters:

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

Excel, VBA, Open FIle DialogMultiple File Types in Folder
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:

Excel VBA, Open File Dialog Filter

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:

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

24 thoughts on “Excel VBA Open File Dialog”

  1. Georg Kroker says:

    Extremely clear and unambiguous, very educational, thank you

    1. pedrumj says:

      No problem, glad I could help 🙂

  2. Sylwia says:

    Now It’s very easy. Thank you so much <3

    1. pedrumj says:

      Glad you found the article useful 🙂

  3. why need name says:

    Microsoft VBScript runtime error: Object required: ‘Application’

    1. pedrumj says:

      Hi there

      Could you please send me the file you are testing the code with.

  4. Isu says:

    I want to open that selected file in separate cells
    can some one help me to rectify the mistake after
    If intChoice 0 Then

    Thx

    Private Sub CommandButton1_Click()

    Dim intChoice As Integer

    ‘Select the start folder
    Application.FileDialog(msoFileDialogOpen _
    ).InitialFileName = “I:Group – Fina”
    ‘make the file dialog visible to the user
    intChoice = Application.FileDialog(msoFileDialogOpen).Show
    ‘determine what choice the user made

    If intChoice 0 Then
    Workbooks.OpenText. Origin:=xlMSDOS, StartRow:=23, DataType:=xlDelimited, TextQualifier:= _
    xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, Semicolon:=False, _
    Comma:=False, Space:=True, Other:=False, FieldInfo:=Array(Array(1, 1), _
    Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1)), _
    TrailingMinusNumbers:=True

    NewPath = Mid(ThisWorkbook.FullName, 1, _
    Len(ThisWorkbook.FullName) – Len(ThisWorkbook.Name)) & “” & “Dunnings – ” & Format(Date, “dd-mm-yyyy”) & “.xlsm”
    ThisWorkbook.SaveAs (NewPath)

    End If

    End Sub

    1. pedrumj says:

      Hi there, could you please clarify what you mean by opening files in separate cells? Are you trying to open the content of text files in different cells?

      1. Isu says:

        I come it this far and mistake is now
        Workbooks.OpenText Filename:= intChoice, Orig…..

        I select a file using open dialog box and now to split into cells I need call that selected file (can be any name when I select)…

        Thx
        Isu

        Private Sub CommandButton1_Click()
        Dim intChoice As Integer
        ‘Select the start folder
        Application.FileDialog(msoFileDialogOpen).InitialFileName = “I:Dunnings”
        ‘make the file dialog visible to the user
        intChoice = Application.FileDialog(msoFileDialogOpen).Show
        ‘determine what choice the user made

        If intChoice 0 Then
        Workbooks.OpenText Filename:= intChoice, Origin:=xlMSDOS, StartRow:=23, DataType:=xlFixedWidth, FieldInfo:= _
        Array(Array(0, 1), Array(6, 2), Array(23, 1), Array(30, 2), Array(63, 2), Array(68, 1), _
        Array(77, 4), Array(88, 4), Array(101, 1), Array(117, 1)), TrailingMinusNumbers:= _
        True
        NewPath = Mid(ThisWorkbook.FullName, 1, _
        Len(ThisWorkbook.FullName) – Len(ThisWorkbook.Name)) & “” & _
        “Dunnings – ” & Format(Date, “dd-mm-yyyy”) & “.xlsm”
        ThisWorkbook.SaveAs (NewPath)
        End If
        End Sub

        1. pedrumj says:

          Could you please send me the following so that I can take a look at your project:

          1- The workbook along with the code you have created.
          2- A few sample files which will be opened using the program
          3- A workbook with the expected results.

          You can find my contact details on the contact page.

  5. Elfer says:

    hello, thank you very much for your article, but I would like to open the selected file from the cell in which the address is saved, or me a hyperlink the selected file appears.

  6. Neeraj says:

    Good stuff ! thanks man

  7. Shereef Hisham says:

    I want to say exactly the same as Georg
    That answer

  8. Shereef Hisham says:

    That answered all my questions.
    Thank you very much!

    Shereef

  9. JL Menu says:

    Many Thanks. This is clear, complete and for once, understandable for non informaticians.

  10. Mark Mayott says:

    Thank you very much. With our impending upgrade from Excel 2010, I’m waiting for our company IT to load Excel 2013 on my machine. Can’t wait to try your code on our macro files.

    Just to confirm, is Application.FileDialog(msoFileDialogOpen).Show compatible with Excel 2013 single document interface (SDI)? Is this the new replacement for Application.Dialogs(xlDialogOpen) – which only seems to work on multiple document interface (MDI) environment? Thanks again!

  11. Sam says:

    Nice clear explanation but .it doesn’t really help me. I want to place restrictions on the left hand part of the file name and that does not seem to work

    For example, I’d like to limit the files from which the user selects to text*.ext
    So the following files are displayed
    text01.ext textabc.ext, text.ext etc

    word01.ext is not displayed

    Is there some way in which I can do this?

    TFAI

  12. Mohsin says:

    Hi, why do you use the call keyword. I always hv the impression that the call keyword is use when you need to call sub routine or function

  13. LUIS RIVERA says:

    Thanks, all the code works fine.

  14. Patrick says:

    I also found the code examples to be very helpful. It was exactly what I was looking for and it solved my problem in minutes. Thank you!

  15. Manoj says:

    Amazing article to open a default file location every time. Thanks!!

  16. valia says:

    Thank you, I was need it a lot…..

  17. Lizzie says:

    Hi

    I am using something similar, but my question is every time I pull in a new file. How can I assign a fixed name to both files, I am trying to write script further with other commands after the files are selected but struggling about referring to the correct file because I don’t know what these files are nameed after selecting them. Please help:

    Dim fNameAndPath As Variant
    Dim fNameAndPath_ As Variant

    ‘first file

    MsgBox “Please select first file”, vbOKOnly

    fNameAndPath = Application.GetOpenFilename(FileFilter:=”Excel Files (*.xlsx), *.xlsx”, Title:=”report1″)
    If fNameAndPath = False Then Exit Sub
    Workbooks.Open Filename:=fNameAndPath

    ‘ second file

    MsgBox “Please select second file”, vbOKOnly

    fNameAndPath_ = Application.GetOpenFilename(FileFilter:=”Excel Files (*.xlsx), *.xlsx”, Title:=”report2″)
    If fNameAndPath_ = False Then Exit Sub
    Workbooks.Open Filename:=fNameAndPath_

  18. Jason says:

    If I want to filter file name included such as “2017-“, no matter they are .xls or pdf or txt, then how to code ? thanks

Leave a Reply

Your email address will not be published. Required fields are marked *