vba - Access/VBA Sub 每隔一段时间工作一次，其他时间抛出 1004 错误

Question

我正在尝试将数据导出到 excel 并使用 VBA 在导出的文件中构建数据透视表。下面的代码在我第一次打开 Access 时运行时完全按照预期运行。第二次运行它时，我得到一个运行时错误'1004'：方法'范围对象'_Global'在我下面标记的行上失败。第三次运行它，它再次完美运行。

我不知道为什么它会起作用然后不起作用。我添加了 wb.Close 和 xl.quit 并在 sub 结束之前将所有内容设置为 Nothing，所以我认为再次运行会很好。

Option Compare Database

Private Sub cmdRunManagerQuery_Click()
    Dim mySQL As String
    Dim Temp As Variant

    DoCmd.TransferSpreadsheet _
        acExport, _
        acSpreadsheetTypeExcel12Xml, _
        "qryManagerQuery", _
        "R:\Workpath\Manager Query.xlsx", _
        True

    Dim xl As Excel.Application
    Dim wb As Excel.Workbook
    Dim ws As Excel.Worksheet
    Dim lRowCount As Long
    Dim myRange As Excel.Range
    Set xl = CreateObject("Excel.Application")
    strInputFile = "R:\Workpath\Manager Query.xlsx"
    Set wb = xl.Workbooks.Open(strInputFile)
    Set ws = wb.Sheets("qryManagerQuery")

    'Test that I can edit the file
    'ws.Range("C250") = "=SUM(C2:C249)"

    Dim pvtCache As PivotCache
    Dim pvt As PivotTable
    Dim StartPvt As String
    Dim SrcData As String
    Dim i As Integer
    Dim pf As String
    Dim pf_Name As String

    pf = "Number of Records"
    pf_Name = "Sum of Number of Records"
    i = 2
    Do While ws.Range("A" & i).Value <> ""
        i = i + 1
    Loop

    'ERROR OCCURS HERE...
    SrcData = ws.Name & "!" & Range("A1:D" & i - 1).Address(ReferenceStyle:=xlR1C1)

    Set sht = Sheets.Add
    StartPvt = sht.Name & "!" & sht.Range("A3").Address(ReferenceStyle:=xlR1C1)
    Set pvtCache = ActiveWorkbook.PivotCaches.Create( _
        SourceType:=xlDatabase, _
        SourceData:=SrcData)

    Set pvt = pvtCache.CreatePivotTable( _
        TableDestination:=StartPvt, _
        TableName:="PivotTable1")

    pvt.PivotFields("1st Level Complete Date").Orientation = xlColumnField
    pvt.PivotFields("1st Level Analyst").Orientation = xlRowField
    pvt.AddDataField pvt.PivotFields("Number of Records"), pf_Name, xlSum

    wb.Save
    wb.Close
    xl.quit
    Set xl = Nothing
    Set wb = Nothing
    Set ws = Nothing
    Set sht = Nothing
    Set pvtCache = Nothing
    Set pvt = Nothing

    MsgBox "Export complete.  Files located at R:\Workpath", _
        vbInformation, _
        "Export Complete"

End Sub

标签： vbams-access