The GetOpenFilename Command in VBA
How often in your VBA code do you have ask for a file name along with its complete path as an input from the user? Most programmers end up using an input box in this situation. However, this is not very convenient for the user. Furthermore, there are chances of “file not found” errors and the requirement for additional error handling code to check whether the inputted file name and path exists. And things get even more messy when multiple files are to be inputted.
What if you can replace this with a single line of code that is easier even for the end user? Well it’s possible using the getopenfilename method.
Let’s start with looking at the syntax. If you wish to jump directly to the examples, click here.
expression.GetOpenFilename(FileFilter, FilterIndex, Title, ButtonText, MultiSelect)
expression: A variable that represents an Application object.
FileFilter: A string specifying file filtering criteria.
FilterIndex: Specifies the index numbers of the default file filtering criteria, from 1 to the number of filters specified in FileFilter. If this argument is omitted or greater than the number of filters present, the first file filter is used.
Title: Specifies the title of the dialog box. If this argument is omitted, the title is “Open.”
ButtonText: Macintosh only.
MultiSelect: True to allow multiple file names to be selected. False to allow only one file name to be selected. The default value is False.
Return Value: complete file name along with the path
So, let’s look at what the getopenfilename method does in its simplest form.
Sub getFileName() Dim filename As String filename = Application.GetOpenFilename() MsgBox filename End Sub
- First it will open a “File Open” Dialog box. The default location is the “Documents” folder
- The user then navigates to the actual file and selects it. The filename along with the path is stored in the variable filename and displayed to the user in a Message Box.
- The variable is set to false if the user cancels on the above window.
Please note, actual file is not opened. Also, If you want to get the current file name and path, you can check the article: Word VBA, Get Current File Path and Name, which very well applies to Excel as well.
Let’s have a look at few examples of how to use GetOpenFilename
Example 1: Specify a title for the dialog box (default is open)
Sub getFileName1() Dim filename As String, title As String title = “Select the file to import” filename = Application.GetOpenFilename(,,title) MsgBox filename End Sub
Here the title of the window is set to “Select the file to import” as seen in the snapshot below:
Here, the filename is displayed in a message box. In real applications, you would use the filename to do something more meaningful.
Example 2: Allow selection of multiple files and loop through to get all the file names
Sub getFileName2() Dim filename As Variant Dim i As Integer i = 1 filename = Application.GetOpenFilename(, , , , True) If filename = False Then MsgBox "No file Selected" Exit Sub End If For Each element In filename Cells(i, 1) = element i = i + 1 Next element End Sub
Here the last parameter (multiselect) for GetOpenFilename is set to true. So, the user is permitted to select more than one file. The if condition checks whether the user selected at least one file. If not, the code exits from the sub.
To loop through all the filenames selected by the user, the for loop is used and the values are stored in the workbook
The filename is declared as a variant data type. This is because if the user does not select any file, false is returned, which is of type Boolean, else the filename is returned which is a string.
Example 3: Use file filters to filter the type of files.
Sub getFileName3() Dim filename As Variant Dim i As Integer Dim filterStr As String filterStr = "Text files(*.txt),*.txt," &amp;amp; "Image Files(*.jpg;*.png),*.jpg;*.png" filename = Application.GetOpenFilename(filterStr) MsgBox filename End Sub
Here we have set 2 separate filters, one for text files and the other for Images. The first string is the display name for the type of file, while the second string is the actual filter using the wildcard character – *. For example, “Text files” is the display name and file type to be filtered is specified by “*.txt” as seen in the snapshot below.
To use multiple file extensions for a single file filter type, separate the wildcard expressions with semicolons; for example, “Image Files(*.jpg;*.png),*.jpg;*.png”. If FileFilter is omitted, this argument defaults to “All Files (*.*),*.*”.
Putting it all together
Sub getFileName() Dim filename As Variant Dim i As Integer Dim filterStr As String, title As String filterStr = "Text files (*.txt),*.txt," &amp;amp; "Image Files (*.jpg;*.png),*.jpg;*.png" title = "Select the file to import" i = 1 filename = Application.GetOpenFilename(filterStr, 2, title, , True) If filename(1) = False Then Cells(1, 1) = "No file Selected" Exit Sub End If For Each element In filename Cells(i, 1) = element i = i + 1 Next element End Sub
Note: The getopenfilename method is very similar to msoFileDialogOpen. You can find further details of this in the article: Excel VBA Open File Dialog
So, to summarize, we can effectively use this function to ask users to select a file (to get its name and path) instead having them to type in the entire file