Tutorial Microsoft Access VBA – Open File Dialog

Welcome to my website and today we are going to learn about Microsoft Access VBA. I am going to show you how to use the FileDialog property to get the selected file name in MS Access 2016. This method should works on previous MS Office version as well. 

Before we go through, here is the program looks like:

So basically, this VBA will open the file dialog and then get the selected file name and then display the file name and path in the Access Form. 

We are going to use one Button and one Label. Now let’s design the Form.

1. Create a new Form in Microsoft Access
2. Switch to Design mode
3. Add one Button (Open File button) and one Label to the form

Let’s Coding

To enter the coding window, you can do the following:

1. Right-click the Open File button
2. Click Build Event
3. Choose Code Builder from the list

Then the Code Builder window will open up. You will need to add the Microsoft Office 16.0 Object Library. To do this, go to Tools | References and then enable the Office 16.0 Object Library from the list and click OK. 

Now here is my code block in the Open File button:

Private Sub Command0_Click()

Dim fDialog As Office.FileDialog

'Clear Label1
Me.Label1.Caption = "Hello"

Set fDialog = Application.FileDialog(msoFileDialogFilePicker)

'Add filter to the fDialog. I want to filter the .xlsx and .xls only.
With fDialog
    .AllowMultiSelect = False
    .Title = "Please select the Excel file"
    .Filters.Clear
    .Filters.Add "Microsoft Excel", "*.xlsx"
    .Filters.Add "Microsoft Excel", "*.xls"
    
    'Show the Open File Dialog
    If .Show = True Then
        Me.Label1.Caption = .SelectedItems(1)
        Else
        MsgBox "You choose Cancel button"
    End If
    
End With
End Sub

Thank you.