Table of contents
Share Post
Effortlessly
Manage Your Projects

120+ Project Management Templates

Seamlessly manage your projects with our powerful & multi-purpose templates for project management.

120+ PM Templates Includes:
  • 50+ Excel Templates

  • 50+ PowerPoint Templates

  • 25+ Word Templates

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 Method Excel Range Object

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])

Here,
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 .

Effortlessly Manage Your Projects and Resources
120+ Professional Project Management Templates!

A Powerful & Multi-purpose Templates for project management. Now seamlessly manage your projects, tasks, meetings, presentations, teams, customers, stakeholders and time. This page describes all the amazing new features and options that come with our premium templates.

Save Up to 85% LIMITED TIME OFFER
Excel VBA Project Management Templates

All-in-One Pack
120+ Project Management Templates

Essential Pack
50+ Project Management Templates

Excel Pack
50+ Excel PM Templates

PowerPoint Pack
50+ Excel PM Templates
MS Word Pack
25+ Word PM Templates
Ultimate Project Management Template
Ultimate Resource Management Template
Project Portfolio Management Templates