Thursday, April 11, 2013

How to add a Browse button to an Access form

First create a form similar to the one depicted here:

Name the browse button "cmdBrowse," name the textbox "txtFile," and name the Import File button "cmdImport." Write the click event of the Browse button as follows:

Private Sub cmdBrowse_Click()
    Dim success As Boolean
    success = browseFiles(Me.txtFile)
End Sub

Create the following function:

Public Function browseFiles(tbx As TextBox) As Boolean
   Dim fileDlg As Office.fileDialog
   Dim theFile As Variant

   ' Clear the textbox contents.
   tbx.Value = ""

   ' Instantiate the File Dialog.
   Set fileDlg = Application.fileDialog(msoFileDialogFilePicker)

   With fileDlg
      ' Set the title of the dialog box. '
      .Title = "Please select one or more files"

      ' Clear the filters and add the ones you want.'
      '.Filters.Add "Access Databases", "*.ACCDB"
      '.Filters.Add "Access Projects", "*.ADP"
      '.Filters.Add "Text Files", "*.TXT"
      '.Filters.Add "CSV Files", "*.CSV"
      .Filters.Add "Excel xlsx files", "*.XLSX"
      .Filters.Add "Excel files", "*.XLS"
      '.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

         'Get the selected file
         For Each theFile In .SelectedItems
            tbx.Value = theFile

      End If
   End With
   browseFiles = True
End Function

Notice how you can add more file types to the file dialog. I've commented out all of the non-Excel file types since the form will be used to import Excel files only. 


  1. Dim fileDlg As Office.FileDialog I'm getting a user defined type not defined error.

  2. All good. Did not have all of my library ref checked off. This works!

  3. This comment has been removed by the author.