Copy FromRecordset Method

For those readers familiar with DAO, CopyFromRecordset is a very powerful method that copies the contents of a DAO Recordset object onto a worksheet, beginning at the upper-left corner of the specified range. Note that if the Recordset object contains fields with OLE objects in them, this method fails.

To illustrate, consider the following code, which requires that a reference to Microsoft DAO is set in the References dialog box in the Excel VBA Tools menu:

Dim rs As Recordset Set rs =

DBEngine.OpenDatabase("d:\excel\excel.mdb"). OpenRecordset("Objects") Range("A1").CopyFromRecordset(rs, 10, 10)

This code opens an Access database named d:\excel\excel.mdb, creates a recordset based on the table named Objects, and then copies the first 10 columns of the first 10 rows of the recordset to the current worksheet, starting at cell A1.

Note that, in general, copying begins at the current row of the Recordset object (which in our example is the first row, since we opened the recordset anew).

+1 0


Post a comment