Thread: Incorrect error Handling

I have some error handling code that is supose to halt or termintate the program in the case that a file cannot be found - this would happen if the date extension of the file does not exist for the date the user selected via the date picker for a textbox. Unfortunately it does not work correctly and causes the code to run continuously without stopping ( a loop).

Please see the error handling below, for any suggestions:

Private Sub cmdReturnMTMPortfolio_Click()
    Dim appXL As Excel.Application
    Dim strFilePath As String
    Dim strFileName As String
    Dim strFilePrefix As String
    Dim strFullFileName As String
    Dim strFile As String
    Dim asofDATE As String
    Dim txtDate As TextBox
    Dim wbk As Excel.Workbook
            
    On Error GoTo HandleErr
    strFilePrefix = "mtm_report_asof_"
    strFilePath = "N:\XXXXXXX\YYYYYY\Assignments and Projects\FCIP Program\Spreadsheet Automation\Product\Excel Automation\Robin\Communication\Portfolio List\"
       
    asofDATE = Me.txtDate.Value
    asofDATE = Format(Me.txtDate.Value, "yyyymmdd")
    
    Set appXL = New Excel.Application
    
    strFullFileName = strFilePrefix & asofDATE & ".xls"
    
    Set wbk = appXL.Workbooks.Open(strFilePath & strFullFileName)
    
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "tempMTMReportData", strFilePath & strFullFileName, True, "MTM Detail!"
    DoCmd.OpenQuery "qryCPNamesForMTMDailyReportParentandChild"
    DoCmd.Close acQuery, "qryCPNamesForMTMDailyReportParentandChild"
    MsgBox "Your MTM file has been imported."
          
ExitHere:
    On Error Resume Next
    appXL.ScreenUpdating = True
    appXL.Quit
    Set appXL = Nothing
    Exit Sub
HandleErr:
    MsgBox Err & ": " & Err.Description
    Resume ExitHere
    Resume
    
End

Sub

Thanks Dave for suggestion. But that does not work.

Dave thanks. I got rid of both lines you said. it still does not work. All I want to do is check to see if the file exist. If it does not a prompt error message should pop-up.

Dave thanks. It's not really working, but I am getting closer. It pops up even if the file exist? Not sure whats wrong here.

Private Sub cmdReturnMTMPortfolio_Click()
    Dim appXL As Excel.Application
    Dim strFilePath As String
    Dim strFileName As String
    Dim strFilePrefix As String
    Dim strFullFileName As String
    Dim strFile As String
    Dim asofDATE As String
    Dim txtDate As TextBox
    Dim wbk As Excel.Workbook
            
    strFilePrefix = "mtm_report_asof_"
    strFilePath = "N:\XXXXXXX\YYYYYYY\Assignments and Projects\ZZZZZZZZZ\Spreadsheet Automation\Product\Excel Automation\Robin\Communication\Portfolio List\"
       
    asofDATE = Me.txtDate.value
    asofDATE = Format(Me.txtDate.value, "yyyymmdd")
    
    Set appXL = New Excel.Application
    
    strFullFileName = strFilePrefix & asofDATE & ".xls"
    If Dir(strFullFileName) = "" Then MsgBox "File does not exist"
    
    Set wbk = appXL.Workbooks.Open(strFilePath & strFullFileName)
    
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "tempMTMReportData", strFilePath & strFullFileName, True, "MTM Detail!"
    DoCmd.OpenQuery "qryCPNamesForMTMDailyReportParentandChild"
    DoCmd.Close acQuery, "qryCPNamesForMTMDailyReportParentandChild"
    MsgBox "Your MTM file has been imported."
          
   
End Sub