使用ADODB将记录集打印到Excel工作表 [英] Using ADODB to print recordset to an Excel sheet
问题描述
我正在尝试使用ADODB连接从查询创建记录集,然后将该记录集的内容复制到一系列单元格中
I'm trying to use an ADODB connection to create a record set from a query, then copy the contents of that record set into a range of cells
下面是我的代码的概要,但是我总是遇到错误
Below is the outline of my code, but I keep getting errors
运行时错误'-2147467259(80004005)'未指定错误
Run-time error '-2147467259 (80004005)' Unspecified error
(我已经用所有大写的虚拟引用替换了我的特定引用)
(I've replaced my specific references with all caps dummy references)
Sub Subroutine()
'establish ADODB connection to retrieve data from TABLE
Dim objConnection As New ADODB.connection
Dim objRecordset As New ADODB.Recordset
With objConnection
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source = MYDATASOURCE" & _
"Extended Properties = Excel 8.0;HDR = Yes;"
.Open
End With
'use query to record data to Excel range
Set objRecordset = objConnection.OpenRecordset("Select * From TABLE Where FieldNm = NAME")
With objRecordset
.Open
.MoveFirst
Do Until .EOF
Worksheets("WORKSHEET").Cells(14, 1) = objRecordset.Fields.Item("FieldNm")
.MoveNext
Loop
End With
End Sub
调试进入。在我的
>使用objConnection
块。在此之前,我在使用 .MoveNext
方法时遇到问题。
The debug goes to the .Open
in my With objConnection
block. Before that I was having problems with the .MoveNext
method.
推荐答案
假设您的SQL正确指定了工作表范围,请考虑调整 With ... End With
块之内和之外的某些项目。
Assuming your SQL is correctly specifying worksheet range, consider adjusting some items in and outside of With...End With
block.
- OpenRecordset 是DAO方法。使用 Recordset.Open for ADO
- 删除第二个
。打开
调用 - 删除内部的记录集名称
With
- 在工作表中向下循环,而不是重新分配相同的单元格
- 使用错误处理获取更多信息,以捕获运行时异常
- OpenRecordset is a DAO method. Use Recordset.Open for ADO
- Remove the second
.Open
call - Remove the recordset name inside
With
- Loop through worksheet down the rows instead of reassign same cell
- Use error handling for more informative error message to capture runtime exceptions
VBA
Sub Subroutine()
On Error Goto ErrHandle
'...same as above...
objRecordset.Open "Select * From TABLE Where FieldNm = NAME", objConnection
With objRecordset
.MoveLast
.MoveFirst
i = 0
Do Until .EOF
Worksheets("WORKSHEET").Cells(14 + i, 1) = .Fields("FieldNm")
i = i + 1
.MoveNext
Loop
.Close
End With
objConnection.Close
ExitHandle:
Set objRecordset = Nothing
Set objConnection = Nothing
Exit Sub
ErrHandle:
Msgbox Err.Number & " - " & Err.Description, vbCritical, "RUNTIME ERROR"
Resume ExitHandle
End Sub
这篇关于使用ADODB将记录集打印到Excel工作表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!