×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Excel CopyFromRecordset limit

Excel CopyFromRecordset limit

Excel CopyFromRecordset limit

(OP)
I have a small app where I use Excel to dump the data from data base.
I create an ADODB recordset and use:

CODE

Range("A2").CopyFromRecordset MyRecordSet 

All works OK, unless I have a recordset with a large amount of data (rows).
This code takes OK recordsets up to 65536 rows of data, but no more.

I have some recordsets that have around 223 915 records, but only 65536 of them end up in my file. The rest are lost (?)

I did try to specify the Max number of rows, but that does not help:

CODE

Range("A2").CopyFromRecordset MyRecordSet, 5000000 

Is there a limit of rows allowed in CopyFromRecordset?
What do I need to do to 'grab' all rows from my rst and dump it into Excel?

Could that be the limit of my computer's memory?

PS. Just tried the same code on other computer with newer version of Excel (1908 with the limit vs 2108 and all is well), so that must be it... (?)

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

RE: Excel CopyFromRecordset limit

There is no CopyFromRecordset limit, at least in excel 2016 (2208), however I found claims referring to excel 365.
I have no problems to get +400k records, 11 columns, with (reference to ADO, both 2.8 and 6.1 version works):

CODE -->

Sub CopyFromAccess()
Dim DBFullName As String
Dim strConn As String, strSQL As String
Dim conn As ADODB.Connection
Dim rsData As ADODB.Recordset
Dim i As Integer

DBFullName = "D:\xxxx.accdb"
strSQL = "SELECT ... FROM ... WHERE ..."

Set conn = New ADODB.Connection
strConn = "Provider = Microsoft.ACE.OLEDB.12.0;"
strConn = strConn & "Data Source=" & DBFullName
conn.Open ConnectionString:=strConn

Set rsData = New ADODB.Recordset

With rsData
    .Open Source:=strSQL, ActiveConnection:=conn
    
    ' headers
    For i = 0 To .Fields.Count - 1
        Range("A1").Offset(0, i).Value = .Fields(i).Name
    Next
    
    ' data
    ThisWorkbook.Worksheets(1).Range("A2").CopyFromRecordset rsData
End With

Set rsDdata = Nothing
conn.Close
Set conn = Nothing
End Sub 

combo

RE: Excel CopyFromRecordset limit

(OP)
Thank you Combo thumbsup2

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

RE: Excel CopyFromRecordset limit

Thanks, only records starting from cursor are transferred in my case. This feature may be used to cut recordset to fit worksheet size.

combo

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close