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.
Syntax
expression.CopyFromRecordset (Data, MaxRows, MaxColumns)
expression A variable that represents a Range object.
Parameters
| 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
Long
Remarks
Copying begins at the current row of the Recordset object. After copying is completed, the EOF property of the Recordset object is True.
Example
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")
.Cells.Clear
For i = 0 To Rst.Fields.count - 1
.Cells(1, i + 1) = Rst.Fields(i).Name
Next i
Range("A2").CopyFromRecordset Rst
.Cells.EntireColumn.AutoFit
End With
Rst.Close
Conn.Close
Set Conn = Nothing
Set Rst = Nothing
Exit Sub
ErrHandler:
MsgBox Err.Description, , "Error"
End Sub