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:
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:
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
Extremely clear and unambiguous, very educational, thank you
No problem, glad I could help 🙂
Now It’s very easy. Thank you so much <3
Glad you found the article useful 🙂
Microsoft VBScript runtime error: Object required: ‘Application’
Hi there
Could you please send me the file you are testing the code with.
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
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?
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
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.
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.
Good stuff ! thanks man
I want to say exactly the same as Georg
That answer
That answered all my questions.
Thank you very much!
Shereef
Many Thanks. This is clear, complete and for once, understandable for non informaticians.
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!
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
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
Thanks, all the code works fine.
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!
Amazing article to open a default file location every time. Thanks!!
Thank you, I was need it a lot…..
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_
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