excel - VBA 运行时错误 3021 - 没有当前记录

Question

我正在尝试将多个数据集导出到相应的新 Excel 文件。

   Public Sub MultipleQueries()

Dim i As Integer
Dim Mailer As Database
Dim rs1 As Recordset
Dim rs2 As Recordset
Dim qdf As QueryDef

Set Mailer = CurrentDb
Set rs1 = Mailer.OpenRecordset("MailerData")
Set qdf = Mailer.CreateQueryDef("CCspl", "PARAMETERS CostCentre Text ( 255 );SELECT MonthlyFteData.CostCentre, MonthlyFteData.EmpName, MonthlyFteData.Workload FROM MonthlyFteData WHERE (((MonthlyFteData.CostCentre)=[CostCentre]))")

For i = 0 To rs1.RecordCount - 1

qdf.Parameters("CostCentre") = rs1.Fields("CostCentre")

    Dim oExcel As Object
   Dim oBook As Object
   Dim oSheet As Object
   Set oExcel = CreateObject("Excel.Application")
   Set oBook = oExcel.Workbooks.Add
   Set oSheet = oBook.Worksheets(1)

Set rs2 = qdf.OpenRecordset()

With rs2

oSheet.Range("A2").CopyFromRecordset rs2
oBook.SaveAs "C:\Users\807140\Downloads\" & rs2.Fields("CostCentre") & ".xlsx"

rs2.Close
oExcel.Quit
Set oExcel = Nothing

End With

rs1.MoveNext
Next i

qdf.Close
Set qdf = Nothing
rs1.Close

End Sub

但我得到运行时错误 3021 - 没有当前记录

我替换了

oSheet.Range("A2").CopyFromRecordset rs2
oBook.SaveAs "C:\Users\807140\Downloads\" & rs2.Fields("CostCentre") & ".xlsx"

和

Debug.Print .RecordCount

我确实得到了 rs2 的适当记录数。

如何修复我的代码以消除错误？

Answer 1

不要使用 For..Next使用 Recordsets 循环。用这个:

Do While Not rs1.EOF
    ' do stuff with rs1
    rs1.MoveNext
Loop
rs1.close

正如瑞恩所写， Dim不属于任何循环，将它们移动到子的开头。

如果这没有帮助，请告诉我们错误发生在哪一行。