Create a Form, add a Listbox control and rename it "FileList".
Add a button to a Form.
Set the OnClick() Event of the Button.
In the VBA Editor go to Tools | References.
Find the Microsoft Office 11.0 Object Library Reference and add it.
This maybe a higher number depending which version of Office you have installed.
Private Sub cmdFileDialog_Click()
' Requires reference to Microsoft Office 11.0 Object Library.
Dim fDialog As Office.FileDialog
'Dim fDialog As Object
Dim varFile As Variant
' Clear listbox contents.
Me.FileList.RowSource = ""
' Set up the File Dialog.
Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
'Set fDialog = Application.FileDialog(3)
' Allow user to make multiple selections in dialog box
.AllowMultiSelect = True
' Set the title of the dialog box.
.Title = "Please select one or more files"
' Clear out the current filters, and add our own.
.Filters.Add "Access Databases", "*.MDB"
.Filters.Add "Access Projects", "*.ADP"
.Filters.Add "All Files", "*.*"
' Show the dialog box. If the .Show method returns True, the
' user picked at least one file. If the .Show method returns
' False, the user clicked Cancel.
If .Show = True Then
'Loop through each file selected and add it to our list box.
For Each varFile In .SelectedItems
MsgBox "You clicked Cancel in the file dialog box."
You might get the following error using the Office.FileDialog so just swap it to the below.
Compile error: User-defined type not defined
'Dim fDialog As Office.FileDialog
Dim fDialog As Object
'Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
Set fDialog = Application.FileDialog(3)
There are 4 options you can choose from as a Dialog but the msoFileDialogOpen and msoFileDialogSaveAs constants are not supported in Microsoft Access.
File picker dialog box.
Folder picker dialog box.
Open dialog box.
Save As dialog box.
If you use the Folder Picker you can't use the Filters.
There are other options you can set.
.InitialView = msoFileDialogViewSmallIcons
Files displayed in a list with detail information.
Files displayed as large icons.
Files displayed in a list without details.
Files displayed in a list with a preview pane showing the selected file.
Files displayed in a list with a pane showing the selected file's properties.
Files displayed as small icons.
Files displayed as thumbnails.
Files displayed as tiled icons.
Files displayed in Web view.
You can also set which Folder will open:
.InitialFileName = "c:\599CD\"
You can change which Filter is selected if there is more than 1.
.FilterIndex = 1
You can also change the text on the OK Button.
.ButtonName = "Choose &Access file"