最快的ADO将粘贴表从sql-server复制到Excel [英] Fastest ADO to copy paste tables from sql-server to Excel

问题描述

我要将数据表从sql-server移到Excel中。

我不需要在记录集中移动就只需要获取数据并将其粘贴到工作表中即可。

I'm moving tables of data from sql-server into Excel.
I do not need to move through the record set only to grab the data and paste it into a worksheet.

我是否对记录集的 Open 方法使用正确的参数？

Am I using the correct arguments for the recordset's Open method?

Dim recSet As ADODB.Recordset
Set recSet = New ADODB.Recordset
aConnection.Open
recSet.Open stringSQL, aConnection, adOpenForwardOnly, adLockReadOnly, adCmdText 
wb.Sheets(sName).Cells(1, 1).CopyFromRecordset recSet
recSet.Close
If Not (recSet Is Nothing) Then
    If (recSet.State And 1) = 1 Then recSet.Close
    Set recSet.ActiveConnection = Nothing
    Set recSet = Nothing
End If

推荐答案

这是我用来从MSSQLServer提取数据的方法，也许对您有用：

This is the approach that I've use to extract the data from MSSQLServer, maybe it will be useful for you:

Sub test()
    Dim Connection As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim QT As Excel.QueryTable
    Dim ConnectionString As String
    Dim SQL As String
    Set Connection = New ADODB.Connection
    Set rs = New ADODB.Recordset

    ConnectionString = ""
    SQL = "SELECT * FROM SomeTable"
    Connection.Open ConnectionString
    rs.Open SQL, Connection, adOpenStatic, adLockReadOnly

    Set QT = ActiveSheet.QueryTables.Add(rs, ActiveSheet.Cells(1, 1))
    QT.Refresh:    rs.Close:    QT.Delete:    Connection.Close
End Sub

这篇关于最快的ADO将粘贴表从sql-server复制到Excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！