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.
- Create a new Form in Microsoft Access
- Switch to Design mode
- 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:
- Right-click the Open File button
- Click Build Event
- 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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
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.
