最快ADO从SQL服务器到Excel中复制粘贴表(Fastest ADO to copy paste

我正在从SQL服务器数据的表到Excel中。
我不需要通过只设置来获取数据，并将其粘贴到工作表中记录移动。

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

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

Answer 1:

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

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

文章来源: Fastest ADO to copy paste tables from sql-server to Excel