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.

Syntax

expression.GetOpenFilename(FileFilter, FilterIndex, Title, ButtonText, MultiSelect)

where

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

 

  1. First it will open a “File Open” Dialog box. The default location is the “Documents" folder
  2. 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.
  3. 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.

 

Examples

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.

Workbooks.Open filename

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," & "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 (*.*),*.*”.

Example 4:

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," & "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

Leave a Reply

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