Range.CopyFromRecordset method (Excel)

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. It's recommended that you set an object variable to the range to which you are copying from the recordset. Failing to do so may cause generic automation errors depending on the recordset and the range.

Example

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

For iCols = 0 to rs.Fields.Count - 1 
 ws.Cells(1, iCols + 1).Value = rs.Fields(iCols).Name 
Next 
ws.Range(ws.Cells(1, 1), _ 
 ws.Cells(1, rs.Fields.Count)).Font.Bold = True
Set PushRange = ws.Range("A2")
PushRange.CopyFromRecordset rs

Support and feedback

Have questions or feedback about Office VBA or this documentation? Please see Office VBA support and feedback for guidance about the ways you can receive support and provide feedback.