CopyFromRecordset of Excel VBA Range method can be used if you want to copy the data from a record set. When we deal with data bases using ADO or DAO, we often retrieve the data using select statement to a record set, and we process to an array or we paste into a range. CopyFromRecordset method of Excel Range object will help to copy the data from record set to a specific range in the Excel using VBA.
VBA CopyFromRecordset Range in Excel- Syntax
Here is the syntax of the CopyFromRecordset method of range object.
Range(“YourRange”).CopyFromRecordset(Data As Unknown,[MaxRows], [MaxColumns])
Data is the record set (rs)
MaxRows are the maximum records to be copied, by default all records will be copied.
MaxColumns are the maximum fields to be copied, by default all fields will be copied.
VBA CopyFromRecordset Range in Excel- Examples
Here is the simple example to use VBA to Copy the record set into Excel Worksheet.Sub CopyFromRecordset_To_Range() Dim sSQLQry As String Dim ReturnArray Dim Conn As New ADODB.Connection Dim rs As New ADODB.Recordset Dim DBPath As String, sconnect As String DBPath = ThisWorkbook.FullName ‘DBPath = "C:InputData.xlsx" sconnect = "Provider=MSDASQL.1;DSN=Excel Files;DBQ=" & DBPath & ";HDR=Yes';" Conn.Open sconnect sSQLSting = "SELECT * From [Sheet1$]" rs.Open sSQLSting, Conn '=>Load the Data into an array ‘ReturnArray = rs.GetRows ”OR” '=>Paste the data into a sheet Sheet2.Range("A2").CopyFromRecordset rs 'Close Recordset rs.Close 'Close Connection Conn.Close End Sub
Here is the full explanation and working example file to download:
Working Example on VBA Range CopyFromRecordset
VBA to Copy the 10 records from the Record Set – Example
Here is the example syntax to copy only the 10 records of the record set:Sheet2.Range("A2").CopyFromRecordset rs,10
VBA to Copy the 5 fields from the Record Set – Example
Here is the example syntax to copy only the 10 records of the record set:Sheet2.Range("A2").CopyFromRecordset rs,,5
CopyFromRecordset method will take some time to copy the data into worksheet. The last two example are generally used while testing a connection to a data base. So that we can save some time .
How do I get the 2nd and the 5th field from the record set? I have the first I have field names already in the spreadsheet, and don’t want to include that and fields 3 and 4.
how do we get the data from ms access table and how we connect to it?
i have used exactly same technique to connect to db(sql server) but the mentioned statement (Sheet2.Range(“A2”).CopyFromRecordset rs) is not copying any record to the sheet. it is not throwing any error as well. Please help.