Thread: Object variable or with block variable not set

This works, but its sloppy. I still cannot figure out a relative path.

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:\CBI-DEPT\Application Delivery\Assignments and Projects\FCIP Program\Spreadsheet Automation\Product\Excel Automation\Robin\Communication\Portfolio List\"
       
    asofDATE = Me.txtDate.Value
    'asofDATE = Format(asofDATE, "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

I did. Thank you. Some errors came up - I did not dig and try to investigate. I have an nearly 1 week extension on this so not all is lost. I need to read up on the file dialog to see how that works in Access 2007. I have some other issues with the code so I will be posting shortly.