Skip to main content

Range.CopyFromRecordset method

Copies the contents of an ADO or DAO Recordset object onto a worksheet, beginning at the upper-left corner of the specified range. If the Recordset object contains fields with OLE objects in them, this method fails.


expression.CopyFromRecordset (Data, MaxRows, MaxColumns)

expression A variable that represents a Range object.


Name Required/Optional Data type Description
Data Required Variant The Recordset object to copy into the range.
MaxRows Optional Variant The maximum number of records to copy onto the worksheet. If this argument is omitted, all the records in the Recordset object are copied.
MaxColumns Optional Variant The maximum number of fields to copy onto the worksheet. If this argument is omitted, all the fields in the Recordset object are copied.

Return value



Copying begins at the current row of the Recordset object. After copying is completed, the EOF property of the Recordset object is True.


This example copies the field names of MyDataSheet from a DAO Recordset object into the first row of Sheet1. The example then copies the recordset onto the worksheet, beginning at cell A2.

Sub ExecuteSQL()
    Dim Conn As Object, Rst As Object
    Dim strConn As String, strSQL As String
    Dim i As Integer, PathStr As String

    Set Conn = CreateObject("ADODB.Connection")
    Set Rst = CreateObject("ADODB.Recordset")

    On Error GoTo ErrHandler
    PathStr = ThisWorkbook.FullName
    Select Case Application.Version * 1
    Case Is <= 11
        strConn = "Provider=Microsoft.Jet.Oledb.4.0;Extended Properties=excel 8.0;Data source=" & PathStr 
    Case Is >= 12
        strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & PathStr & ";Extended Properties=""Excel 12.0;HDR=YES"";"""
    End Select
    Conn.Open strConn
    strSQL = "select * from [MyDataSheet$]"
    Set Rst = Conn.Execute(strSQL)
    With Sheets("sheet1")
        For i = 0 To Rst.Fields.count - 1
            .Cells(1, i + 1) = Rst.Fields(i).Name
        Next i
        Range("A2").CopyFromRecordset Rst
    End With

    Set Conn = Nothing
    Set Rst = Nothing
    Exit Sub

    MsgBox Err.Description, , "Error"
End Sub

Leave a comment

Your email address will not be published. Required fields are marked *

Format your code: <pre><code class="language-vba">place your code here</code></pre>