VBA CopyFromRecordset Range in Excel with Examples

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.



50+ Project Management Templates Pack
Excel PowerPoint Word

Advanced Project Plan & Portfolio Template
Excel Template

Business Presentations Templates Pack
PowerPoint Slides

20+ Excel Project Management Pack
Excel Templates

20+ PowerPoint Project Management Pack
PowerPoint Templates

10+ MS Word Project Management Pack
Word Templates

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

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
'=>Paste the data into a sheet
Sheet2.Range("A2").CopyFromRecordset rs
'Close Recordset
'Close Connection
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 .



  1. Max February 26, 2016 at 10:03 PM - Reply

    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.

  2. kapil August 22, 2016 at 12:29 AM - Reply

    how do we get the data from ms access table and how we connect to it?
    please help

  3. prabhat January 23, 2017 at 8:12 PM - Reply

    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.

Leave A Comment

Related pages

excel dynamic chartshow to duplicate excel worksheetlock excel worksheetvba code for vlookupunhide all rowsvba msgbox inputpivot table on excel 2010vba input message boxvba write text fileexcel pivot graphaccess vba casewhat is sumif in excelvba advanced filtercreating hyperlinks in excelexcel 2007 match functioncreate formula in excel vbaworksheet tabscharting with excelexcel unhide columnworkbook close vbaexcel scrum templatevba msgbox buttonscreate an excel macrounhide rowsstring excel vbaexcel vba macrosarray excel macroexcel vba copy sheet to endexcel how to merge columnsmerge and centre excelexcel unhide all rowsadvanced vba tutorial pdfvba worksheet cellsexcel password lockhow to do pivot tables in excel 2013project plan gantt chart excel templateestimating spreadsheet templatemicrosoft excel match functionexcel vba breakactivecelltrending analysis excelvba function exampleshow hidden columns in excelvb6 mysql connection stringhow to do a vlookup for dummiesunhidingdim conn as new adodb connectionunmerge cells in excel 2010excel if cell contains text thenexcel tick boxperformance dashboards in excelhow to insert multiple columns in excelenable developer tab in excel 2010how do you do a checkmark in exceldelete named range in excellearn vbaworkbook excel vbameaning of hlookup in excelvba print sheetproject plan calendar template excelremove sheet protection excelreplace function in excel 2007pivot table tutorial excel 2007vba to printexcel remove headerinterview worksheetiferror functionfinding duplicate records in excelmerge and center on excelvba excel functionvba range syntax