How to pop up the Open file dialog in Excel VBA?
Did you write a piece of VBA code where you need to process a file, or copy and paste something in to another file?
It works very well in your test code with the path pointing to that file.
But what if the file is not static?
What if they archiving the file every month?
You need to give a method to your users so they can easily update the path any time.
The good news:
You can invoke the windows open file dialog box to do this.
Let’s take a look at the code.
In the code on the right.
The parameters are set to:
-NOT allow the user to select multiple files
-The title of the window will be: Please select the Shift Report
-The dialog window is set to only show xlsx files in any folder
The selected file will be taken in the txtfilename variable. (not declared!)
POP UP A FILEPICKER IN EXCEL VBA
Sub Filepicker() Dim filepicker As Office.FileDialog Set filepicker = Application.FileDialog(msoFileDialogFilePicker) With filepicker .AllowMultiSelect = False .Title = "Please select the Shift Report" .Filters.Add "xlsx", "*.xlsx" .Filters.Add "All", "*.*" If .Show = True Then txtFileName = .SelectedItems(1) End If End With End Sub
If you want to play around with the Application.Filedialog properties I can recommend you the Microsoft Application object reference library
How to add this code to my workbook?
Time needed: 3 minutes.
If you want to implement this code above:
- Copy the code from above
Highlight and Ctrl+C
- Open your VBA Editor in Excel
Go to your workbook and hit Alt+F11. This will open the VBA Editor window
- If you don’t have a module insert one
Go to insert in the VBA Editor and select module.
- Paste the code from above
Click the module that you inserted, then in the main window hit Ctrl+V to paste the code.
Note: If you have Option Explicit on the top of the main window you need to delete that line for the code to work.
Further explanation of the Option Explicit line in Excel VBA HERE
- Hit F5 to run your macro
If you liked this post why don’t you check out some of our other topics?
There is a great one one on the Excel Quick Access Toolbar
Come and visit us.
Come and visit excelangel on Facebook to find and join the growing community of true office workers.
Oh, I almost forgot.
Drop us a like while you there…