我想要做的就是使用VBA将某些查询的结果转换为Excel工作簿 . 主要问题是openRecordSet方法似乎无法正常工作 . 每当我尝试调试它时,我都会看到记录集(rcset)是Nothing . 当我只运行查询以便在Access查看器中查看时,它似乎工作得很好(参见最后一行代码) . 我运行代码时没有错误,所以我很难理解为什么我的记录集会返回Nothing . 我在互联网上搜索了很多,但在这种特殊情况下没有找到任何人 . 在此先感谢您的帮助!
Dim db As DAO.Database
Dim qdef As DAO.QueryDef
Dim rcset As DAO.Recordset
Dim i As Integer
'Identify the database and query
Set db = CurrentDb
On Error Resume Next
With db
.QueryDefs.Delete ("RealQuery")
Set qdef = .CreateQueryDef("RealQuery", strSQLRQ)
.Close
End With
'The problem child line
Set rcset = qdef.OpenRecordset()
'Clear previous contents
Dim xlApp As Object
Set xlApp = CreateObject("Excel.Application")
With xlApp
.Visible = True
.Workbooks.Add
.Sheets("Sheet1").Select
'Copy the recordset to Excel
.ActiveSheet.Range("A2").CopyFromRecordset rcset
'Add column heading names to spreadsheet
For i = 1 To rcset.Fields.Count
xlApp.ActiveSheet.Cells(1, i).Value = MyRecordset.Fields(i - 1).Name
Next i
xlApp.Cells.EntireColumn.AutoFit
End With
qdef.Close
Set qdef = Nothing
Set db = Nothing
DoCmd.OpenQuery "RealQuery", acViewNormal