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.Clear
      '.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
         Next

      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. 

Regular Expressions in Access VBA

To use regular expressions in Access, make sure you add Microsoft VBScript Regular Expressions as a reference in your application. The following example was created in Access 2010.

Regular expressions are always a bit mysterious to the uninitiated. It helps to have a concrete example to get started. Once you break down the meaning of each piece of the expression, the whole thing starts making sense.

In this example, I'm validating social security numbers (SSNs). I wanted a validator that would work with or without the dashes. Here's the pattern used in the validator function:

re.Pattern = "^\d{3}-?\d{2}-?\d{4}$"

The "^" indicates the start of the line or string. The "\d{3}" means the SSN has to start with 3 digits. The "-?" means the 3 digits can be followed by 0 or 1 occurrence of a dash. "\d{2}" means there must be 2 digits following the dash (if there is one). Then we check for 0 or 1 occurrence of a dash again ("-?") followed by 4 more digits ("\d{4}"). The "$" indicates the end of the line or string.

If you wanted to validate dash-free SSNs, you would write an expression like this: "^d{9}$". This expression simply checks for 9 consecutive digits.

For more information on how to make your own pattern-matching expressions, see the Wikipedia article on regular expressions.

Here's the complete validator function:

Public Function validateSSN(ssn As String) As Boolean

    Dim re As RegExp
    Dim matches As MatchCollection
    Set re = New RegExp
    re.IgnoreCase = True
    re.Global = True
    re.Pattern = "^\d{3}-?\d{2}-?\d{4}$"
    Set matches = re.Execute(ssn)
    If matches.Count <> 1 Then
        validateSSN = False
        Exit Function
    End If
    
    validateSSN = True
        
End Function