Блог

Проблема возникает ближе к концу в appXL. Запустите «MTD».

 Public Function mtd_only()
On Error GoTo Err_mtd_only

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim intStart As Integer
Dim appXL As Excel.Application
Dim RptDt As Date
Dim MoNo As Integer
Dim morningreport As Object
Dim monthtodate As Object
Dim DER As Object
Dim FY As Integer

    Select Case MoNo
Case DLookup("Month", "report dates") > 3
MoNo = DLookup("Month", "report dates")   9
Case Else
MoNo = DLookup("Month", "report dates") - 3
End Select

FY = DLookup("FY", "report dates")

        RptDt = DLookup("Report", "report dates")

        Set dbs = CurrentDb

        Set appXL = New Excel.Application
        
        appXL.Visible = False

'Open the MTD and activate the claims data sheet
        
       Set monthtodate = appXL.Workbooks.Open("C:_testA Daily 2 - FY" amp; FY amp; " MTD" amp; MoNo amp; " " amp; MonthName(Month(RptDt)) amp; " " amp; Year(RptDt) amp; "" amp; MonthName(Month(RptDt)) amp; " " amp; Year(RptDt) amp; " QIDR MTD.XLSM")
        appXL.Worksheets("Claims Data").Select



'Select the data you want to output
        
        Set rst = dbs.OpenRecordset("SELECT * FROM MorningReport;")
        
'======
'Append

'Find the current number of rows, will start from row 2 on a blank sheet though
        
        intStart = appXL.ActiveSheet.Range("A1").CurrentRegion.Rows.count   1
        appXL.ActiveSheet.Range("A" amp; intStart).CopyFromRecordset rst
    
'Select the data you want to output
       
        Set rst = dbs.OpenRecordset("SELECT * FROM [MTD Sales];")
        
'activate the sales data sheet
        
        appXL.Worksheets("Sales Data").Select
        
        
'=========
'Overwrite
        
        appXL.Range("A2:J45500").Select
        appXL.Selection.ClearContents
        appXL.ActiveSheet.Range("A2").Select
        appXL.ActiveSheet.Range("A2").CopyFromRecordset rst
        
        
        If LastWorkDayOfMonth(RptDt) = RptDt Then
                'DoCmd.OpenQuery "Append FYTD Sales", acViewNormal, acReadOnly
            End If
        
'===============================================

'Select the data you want to output
        
        
        Set rst = dbs.OpenRecordset("SELECT * FROM [DER Sales];")
        
'activate the daily sales sheet
        
        appXL.Worksheets("Daily Sales").Select
        
        
'=========
'Overwrite
        
        appXL.Range("A2:E1000").Select
        appXL.Selection.ClearContents
        appXL.ActiveSheet.Range("A2").Select
        appXL.ActiveSheet.Range("A2").CopyFromRecordset rst
        
    Set rst = dbs.OpenRecordset("SELECT * FROM [DRT_Claims];")
        appXL.Worksheets("DRT Claims").Select
        
        intStart = appXL.ActiveSheet.Range("A1").CurrentRegion.Rows.count   1
        appXL.ActiveSheet.Range("A" amp; intStart).CopyFromRecordset rst
        
        
'===============================================
'Save and Format MTD Report
        
        appXL.Run "MTD"
        appXL.ActiveWorkbook.Save
        appXL.Quit
        Set appXL = Nothing
   
'Revive Warnings
DoCmd.SetWarnings True

Exit_mtd_only:
    Exit Function

Err_mtd_only:
    MsgBox Err.Description
    Resume Exit_mtd_only

End Function