I want add a custom error message to an Excel import error

[templeowls]

Hi I've got the below code that users will use to import an excel spreadsheet into access. While working on it, I found out that it won't import the excel doc in unless the file name and sheet name in excel match. If they don't, it just won't import the spreadsheet in and will say "the search key is not found". Is it possible to change that error message to be more specific? Any idea how to code that if so?

Private Sub bttnProcessIt_Click()
Dim wdShell As Object, strFile_Path As String, strResponse As String, fd As FileDialog

 On Error GoTo ImportIt_Err

    MsgBox "Please remember that the file name AND the sheet name in Excel must be named 'CRM'.", vbOKOnly
  

    ' Prompt user for file path for the Raw CRM spreadsheet
    Set fd = Application.FileDialog(msoFileDialogFilePicker)
    With fd
        .Title = "Please select the CRM file for processing"
        .InitialFileName = "F:\CRM"
        .Filters.Add "Excel Spreadsheets", "*.xlsx", 1
        .FilterIndex = 1
        .Show
        If .SelectedItems.Count = 0 Then
            Exit Sub
        Else
            strFile_Path = .SelectedItems.Item(1)
        End If
    End With
              
    DoCmd.SetWarnings (False)
    DoCmd.OpenQuery "qryClear_RawAuditTemp"
    DoCmd.OpenQuery "qryClear_RawAudit"
    DoCmd.RunSavedImportExport "Import-CRM"
    DoCmd.OpenQuery "qryCleanRawAuditData"
    DoCmd.RunSavedImportExport "Export-Last Name Match Report"
    DoCmd.RunSavedImportExport "Export-First and Last Name Match Report"
    DoCmd.RunSavedImportExport "Export-Address Match Report"
    DoCmd.RunSavedImportExport "Export-Phone Number Match Report"
    DoCmd.RunSavedImportExport "Export-Employee Views Report"
    DoCmd.RunSavedImportExport "Export-No Action Views Report"
    DoCmd.RunSavedImportExport "Export-Summary Report"
    DoCmd.Close acForm, Me.Name

    strResponse = MsgBox("The CRM audit has been successfully imported and the exported files are located in CRM Audit Tool folder!")

ImportIt_Exit:
    Exit Sub

ImportIt_Err:
    MsgBox Error$
    Resume ImportIt_Exit
End Sub

[June7]

Have an If Then Else block in the error handler.

If Err.Number = whatever error number you want to trap Then

Review http://allenbrowne.com/ser-23a.html

[templeowls]

This doesn't give me an error number tho..

[Micron]

When you raise the error, don't you get the number? Use that number?
When you raise an error, your procedure leaves warnings turned off - possibly for the whole db session.

[templeowls]

No i get the below error message. For the uninitiated who are using the database, they'd have no idea what that meant. Which is why I'd like to customize the wording (if possible)

[Micron]

Not sure what the error number is for that. If Google doesn't tell you, add either a debug.print or message box: Msgbox err.number and see what you get. It might be an error that you can't trap (i.e. one that starts with a minus sign in front of a very long number).
What would you tell anyone anyway? You have a message in mind because you know what the problem is? You're sure it's that the sheet and file name don't match and not the more typical reason of having leading spaces in Excel column names?

[June7]

Your error handler is only giving error message in MsgBox. Change it to include Err.Number. Or disable the error handler so you will get a intrinsic run-time error popup so you can see the number. Then modify error handler. Did you review the linked tutorial?

And yes, need to turn warnings back on. Do it in the Exit block. Could close form there as well.

Would really be better to prevent error. Compare names before attempting import.