Any idea how to prevent error message when a user exits out of excel import?

[gojets1721]

Hi I have a code written (see below) that prompts the user upon opening access to import in an excel file. Its a form and has just a big 'import' button. When the user presses it, the windows dialog box opens for them to select a file. However, if they press cancel or X out of the dialog box, a nasty "run time error 5" error box appears. Now the user can just select 'end' and get out of it, but it would be nice if it was possible for them to be sent just back to the form if they cancel out with no error message appearing. Any idea?

Public Sub bttnProcessIt_Click()

Dim wdShell As Object

' 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
    Application.FileDialog(msoFileDialogOpen).Title = "Please select the CRM file for processing"
    Application.FileDialog(msoFileDialogOpen).InitialFileName = "F:\CRM"
    Application.FileDialog(msoFileDialogOpen).Filters.Add "Excel Spreadsheets", "*.xlsx", 1
    Application.FileDialog(msoFileDialogOpen).FilterIndex = 1
    Application.FileDialog(msoFileDialogOpen).Show
    strFile_Path = Application.FileDialog(msoFileDialogOpen).SelectedItems.Item(1)
           
    DoCmd.SetWarnings (WarningsOff)
    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

 

[theDBguy]

Uncomment the On Error line.

 

[gojets1721]

Like remove just this line?

' On Error GoTo ImportIt_Err

I took it out but the error is still appearing when I exit out of the dialog

 

[theDBguy]

Like remove just this line?

' On Error GoTo ImportIt_Err

I took it out but the error is still appearing when I exit out of the dialog

No, I meant just remove the apostrophe at the beginning of that line.

 

[gojets1721]

Oh jesus I'm dumb.

That kinda worked. Now it says "invalid procedure call or argument". Anyway to just not have that appear and it returns to the form?

 

[theDBguy]

Oh jesus I'm dumb.

That kinda worked. Now it says "invalid procedure call or argument". Anyway to just not have that appear and it returns to the form?

If you want to ignore the error message, comment out the MsgBox Error$ line.

 

[gojets1721]

That worked!! Thank you!!

 

[theDBguy]

That worked!! Thank you!!

It wasn't the best approach, but it works for now. Good luck with your project.

 

[gojets1721]

Just out of curiosity, what's that MsgBox Error$ line for? I inherited this database; I did not write it myself. So I'm kinda learning on the fly

 

[theDBguy]

Just out of curiosity, what's that MsgBox Error$ line for? I inherited this database; I did not write it myself. So I'm kinda learning on the fly

It's to tell the user there was an error, any error.

 

[Isaac]

@templeowls
You shouldn't just allow the problem to hit the error handler and walk away/call it good. This will have your users wondering why they were presented with an error when in reality, they just cancelled out of the dialogue. They will expect you to have thought of that and covered it.

I would recommend that you actually test if strFile_Path has a value and if not, Exit Sub. Here is the code I would recommend you use instead.

Also, you need to have "Option Explicit" at the TOP of every code module. This will force you to declare variables and remind you if you fail to. You should go to tools > options in your vba program and check 'Require Variable Declaration' which will help "Option Explicit" be placed at the top of every new module. for now, place it manually. Then, you should never use a database that won't compile, which I don't think yours will (I could be wrong on that). Click Debug > Compile and solve all errors before using any database system.
These are things to work on - basics of using and maintaining a healthy vba project

In the meantime, here is workable code:

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