MS Access - Import data from Excel to Access using Criteria

Function selectFile()
    Dim fd As FileDialog
    Set fd = Application.FileDialog(msoFileDialogFilePicker)
 
    With fd
        If .Show Then
            selectFile = .SelectedItems(1)
        Else
            'stop execution if nothing selected
            End
        End If
    End With
 
    Set fd = Nothing
End Function


Private Sub Command0_Click()
On Error GoTo ErrorHandler
    'disable ms access warnings
    DoCmd.SetWarnings False
 
    'Delete existing data in table
    'This is optional. If you are combining spreadsheets
    'in your table, don't run this query
    'DoCmd.RunSQL "DELETE * FROM names"
 
    'load spreadsheet
    DoCmd.TransferSpreadsheet acImport, 8, "tblCapacity", selectFile(), True
 MsgBox "Import Successful"
    're-enable ms access warnings
    DoCmd.SetWarnings True
 
Exit Sub
 
ErrorHandler:
MsgBox "There was an Error: " & Err & ": " & Error(Err)
End Sub

Select allOpen in new window