使用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屋！