最快的ADO将粘贴表从sql-server复制到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

相关推荐