Written by:

Written by:

Thomas Szigeti

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:

  1. Copy the code from above

    Highlight and Ctrl+C

  2. Open your VBA Editor in Excel

    Go to your workbook and hit Alt+F11. This will open the VBA Editor windowExcel VBA Editor window

  3. If you don’t have a module insert one

    Go to insert in the VBA Editor and select module. Excel VBA Editor - insert module

  4. 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
    Excel VBA Paste code

  5. Hit F5 to run your macro

    Excel VBA How to run code

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…

[et_pb_ccfcm_facebook_comments_module ccfcm_app_id=”339898187197719″ _builder_version=”4.6.1″ _module_preset=”default”][/et_pb_ccfcm_facebook_comments_module]