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.

PREMIUM TEMPLATES LIMITED TIME OFFER

ON SALE80% OFF

50+ Project Management Templates Pack
Excel PowerPoint Word

Advanced Project Plan & Portfolio Template
Excel Template

Ultimate Project Management Template
Excel Template

20+ Excel Project Management Pack
Excel Templates

20+ PowerPoint Project Management Pack
PowerPoint Templates

10+ MS Word Project Management Pack
Word Templates


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 .


 
Related Resource
Excel VBA Reference Project Management Reference
VBA Reference: Excel Reference:
External VBA Reference
Excel VBA Reference Project Management Reference
VBA Reference:
Excel Reference:

3 Comments

  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

This site uses Akismet to reduce spam. .


Related pages


excel vba application.getopenfilenamevisual basic check boxsurface chart in excelhow to unhide columnscan you delete duplicates in excelexcel 2010 data validationvba this worksheetactivecell rangepopulate listbox vbaduplicate finder excelcreate project timeline in excelunprotect excel sheet without password 2007delete duplicate columns in excelddl dml commandsexcel vba rows selectapplication inputboxunlock excel passwordvba loops examplesexcel uppercasearray in excel vbaiserror formulavba create userformrange reference vbaexcel sql vbahow to convert column to row in excelbest excel book for data analysislogical functions in excel 2007excel auto adjust row heightmicrosoft excel test questions and answersvlookup and hlookup in excel 2007unprotecting an excel spreadsheetcommonly asked interview questions with answers for freshersexcel unhide column asql questions and answers pdfvba absolute valuebubble charts excelusing match function in excelexcel averageif examplevba odbc connection stringvba macros excelexcel for loopsms access vba tutorialvlookup between two valuesnested vlookuphow to delete duplicate information in excelhow to unhide a row in excelhow to unhide column in excelvba excel sort columnhow to unshare a workbookhow to fix a column in excelhow to learn excel vbavba lookupexcel template dashboardcrlf vbaconvert an excel file to csvsurface plots in excelswot analysis presentation templateformula to hide rows in excelvba cells rangeexcel scripting tutorialhyperlink excel sheetexcel deduplicateexcel vba step by step tutorialadvanced filter in excel 2013unprotect excel sheet without passwordsumif excel 2007what is isna in excelvba combobox codesubtract two dates excelmacro function in exceldefined names in excel