There are times in Microsoft Excel or Access when you may require user input regarding the location of a file.  For example, you may need to export some data in Access, but need to prompt the user regarding where the data should be saved.

For this example, we will create a simple Access form with one button.  Although we are using Access here, the core logic can be used from VBA in any Microsoft product (e.g. Excel, Word).

1. Create a Button

Create a basic form and add a button.

To add code to this button’s event, go to the Property Sheet->Event for the new button and click on ... for the On Click event.  In the “Choose Builder” pop-up, select “Code Builder”.

2. Import Required VBA Reference

Import the VBA reference required to use the file dialog by going to Tools->References in the VBA editor menu.

Scroll down to the Microsoft Office “VERSION” Object Library entry (Office version may vary), check it, and click OK.

3. Write Event Code

Add the code below to the button’s on-click event procedure.

Private Sub Command0_Click()

    Dim fd As FileDialog
    Dim fileName As String
    
    Set fd = Application.FileDialog(msoFileDialogOpen)
    
    'If you'd like to add any filters to what type
    'of file can be selected, use the section below.
    'For the example, we will add a filter for text
    'files onlly.
    With fd
        'Existing filters must be cleared before adding new:
        .Filters.Clear
        .Filters.Add "Text Files", "*.txt"
        'InitialFileName can be used to set initial directory for file dialog:
        .InitialFileName = "C:\Users\Public"
    End With
    
    fd.Show 'Show file select dialog
    
    'Set variable "filename" equal to path of file selected:
    fileName = fd.SelectedItems(1)
    
    'You can now use the filename for the file selected in your code:
    MsgBox fileName
    
End Sub

4. Test Button

Save the code, go back to the form, and click the button.  You should see the file dialog appear. Select your file, and you should see that the VBA code is able to reference its path.