Dynamic User File Selection Using
Excel VBA GetOpenFilename Method
This post will demonstrate how we can allow a user to select a file using a traditional “open file”-type dialog box.
This has tremendous advantages over hard-coded solutions where the filename and its location are statically defined in either VBA code…
…or captured from a cell’s formula result.
Giving the user the ability to pick their file allows us to execute the VBA code on any selected file. We can open the file, read information form the file, and manipulate the file’s contents.
Our objective is to write a simple macro that prompts the user for a file using a traditional “file open” dialog box. With this information, we will open the file in the background, copy a range of data, paste it into our active worksheet, then close the user-selected file.
Open the VBA Editor
to Start Writing Code
Begin by opening the Visual Basic Editor by pressing ALT-F11 on the keyboard.
The process of presenting a dialog box to open files can be accomplished by accessing a built-in method of the Application object. The method is called GetOpenFilename.
To use the GetOpenFilename method in a macro, start a new procedure and enter the following code.
Sub Get_Data_From_File() Application.GetOpenFilename End Sub
To test, run the macro to watch as Excel presents us with the Open dialog box.
Notice that the title of the dialog box is “Open” and the default file type filter is to display all file types.
These are customizable options.
If we cancel the macro or select a file and click Open, nothing happens.
This is because the purpose of the GetOpenFilename method is to capture the name of a selected file; nothing more. The act of opening the file would need to be performed by a separate piece of code.
To make this method useful, we will store the selected file in a variable then use the variable in an Open method to perform the file open operation.
The syntax for the GetOpenFilename method is:
GetOpenFilename( FileFilter, FilterIndex, Title, ButtonText, MultiSelect)
There are several customizable options with the GetOpenFilename method.
- FileFilter –defines the pattern by which files are filtered. If you only wish to display text files (files with a .TXT or .CSV extension) you would define the filter as (“Text Files (*.TXT; *.CSV), *.txt; *.csv”).
- FilterIndex – specifies the default filter from the list supplied in the FileFilter If not defined, the first item in the list is selected as the default.
- Title – specifies the text to be displayed in the title bar of the dialog box.
- ButtonText – customizes the text for the buttons (Macintosh only).
- MultiSelect – Use TRUE to allow multiple files to be selected. Use FALSE to only allow a single file selection. FALSE is the default choice.
If you’d like to understand how to use MultiSelect correctly, check out the complete course.
Updating the Code
The following are modifications we will make to our existing code:
- We want to capture the user’s selection in a variable named “FileToOpen”. This will be declared as a string data type.
- We will use an SET assignment statement to place the user’s selection in the “FileToOpen” variable.
- We will customize the title of the dialog box to read “Browse for your File & Import Range”.
- Finally, we will only display Excel files in the dialog box; i.e. files ending in .XLS, .XLSX, .XLSM, or .XLSB.
Using the argument positions to define the options, the updated code will appear as follows:
Sub Get_Data_From_File() Dim FileToOpen as String FileToOpen = Application.GetOpenFilename(“Excel Files (*.xls*), *xls*”, , “Browse for your File & Import Range”) End Sub
Using the option titles to define the options, the updated code can be made more readable:
Sub Get_Data_From_File() Dim FileToOpen as String FileToOpen = Application.GetOpenFilename(Title:=“Browse for your File & Import Range”, FileFilter:= “Excel Files (*.xls*), *xls*”) End Sub
Testing the Code
When we run the code, we are presented with the following (note the custom title and filters):
Selecting a file and clicking Open returns us back to Excel with no action. We’re still not convinced anything happened.
To see the user’s file selection captured as a variable, perform the following:
- Add a breakpoint to the “End Sub” step of the code (click the light gray column to the left of the “End Sub” line of code)
- Highlight the “FileToOpen” variable
- Right-click the highlighted variable and select “Add Watch…”
- Click OK to add the watch (the Watch window will open automatically)
Run the code.
After selecting a test file and clicking Open, the code will pause on the final line.
Examining the Watch window, we can see the selected file name has been captured and stored in the “FileToOpen” variable. (NOTE: You can also hover your mouse pointer over any reference to “FileToOpen” and observe a pop-up that displays the variable’s contents.)
Cancelling Our Choice
Suppose the user cancels the Open dialog box. How will the code respond to this action?
If we launch the code and press the Cancel button without selecting a file, the GetOpenFilename method returns a “False” response and stores it in our “FileToOpen” variable.
Because the “FileToOpen” variable was declared as a string data type, the word “False” will be interpreted as the filename to be opened.
Fixing the Code
Because we want to capture a text string when the user selects a file or capture a Boolean response when the user clicks the Cancel button, we need to change the data type of our “FileToOpen” variable to a Variant data type.
Dim FileToOpen as VariantDim FileToOpen as Variant
If we execute the code with this modification, we can see when we pause on the last line that the “FileToOpen” variable is holding a Boolean FALSE response, not a text response.
We will test the “FileToOpen” variable for the presence of a Boolean FALSE. If the variable is NOT False, we will execute the remainder of the code. Otherwise, we will do nothing.
Sub Get_Data_From_File() Dim FileToOpen As Variant FileToOpen = Application.GetOpenFilename(Title:="Browse for your File & Import Range", FileFilter:="Excel Files (*.xls*),*xls*") If FileToOpen <> False Then (Things happen here) End If End Sub
The below code contains instructions for what is to be performed when a valid filename is supplied.
We have added an additional variable named “OpenBook” to store the contents of the selected file. The “OpenBook” variable is declared as a Workbook data type.
The additional code will perform the following tasks:
- Open the selected file
- Store the contents of the file in the variable “OpenBook”
- Select the first sheet in the workbook
- Copy the contents of cells A1 through E20
- Paste the copied data as values into the file named “SelectFile” starting in cell A10
- Close the workbook selected by the user
Sub Get_Data_From_File() Dim FileToOpen As Variant Dim OpenBook as Workbook FileToOpen = Application.GetOpenFilename(Title:="Browse for your File & Import Range", FileFilter:="Excel Files (*.xls*),*xls*") If FileToOpen <> False Then Set OpenBook = Application.Workbooks.Open(FileToOpen) OpenBook.Sheets(1).Range("A1:E20").Copy ThisWorkbook.Worksheets("SelectFile").Range("A10").PasteSpecial xlPasteValues OpenBook.Close False End If End Sub
Optimizing the Code
To reduce screen flicker when opening background files, we will add the “Application.ScreenUpdating” toggle to our code.
We will place the toggle at the beginning with a FALSE setting and the same toggle at the end of the code with a TRUE setting.
Sub Get_Data_From_File() Dim FileToOpen As Variant Dim OpenBook as Workbook Application.ScreenUpdating = False (Original OPEN and IF operations go here) Application.ScreenUpdating = True End Sub
Defining Multiple Filters
If you wish to filter for a variety of file type; such as Excel files, text files, or all files, you can define your FileFilter argument as follows:
Application.GetOpenFilename("Excel Files (*xls*), *xls*, Text Files (*.TXT), *.txt, All Files (*.*), *.*")
Notice when run, the list has been filtered for Excel files, but you are provided a dropdown to select one of the other two categories of files.
If we add a value to the FilterIndex option, we can pre-select one of the defined filters to be a default choice. For our three filters, we would define the following values:
1 – for Excel files
2 – for Text files
3 – for All files
The updated code will appear as follows if we wish for Text files to be the default filter selection.
Application.GetOpenFilename("Excel Files (*xls*), *xls*, Text Files (*.TXT), *.txt, All Files (*.*), *.*", 2)
Feel free to Download the Workbook HERE.
Unlock Excel VBA & Macros Course is here.
Save time. Achieve more.
Over 50 Excel macro examples for download & useful VBA codes you can use for your work.
Learn the WHY not just the HOW