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.
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.
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