By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,720 Members | 2,244 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,720 IT Pros & Developers. It's quick & easy.

How to export data to excel when Records are more and than 65000 rows

P: 91
Hey People,

I using access 2003 and excel 2003 when access returns more than 65000 rows

Is their any thing to split the data while exporting or any other solution

Please help On the same
Aug 21 '12 #1
Share this Question
Share on Google+
4 Replies

Expert Mod 5K+
P: 5,397
There is no way to exceed the maximum number of rows in a single data sheet within an excel 2003 workbook.

You can split the data across multiple worksheets by using several different methods.
Aug 21 '12 #2

P: 91
thanx for your reply is their any method in access so that the query would get split in different sheets
Aug 21 '12 #3

Expert Mod 2.5K+
P: 3,283

It depends on how many records you might have to export. But I would create code that cycles through the records in groups of 65,000, and then export a query, based on that group of records, to different worksheets, using a variable Counter that increases each time you reach a new group of 65,000. Basics below:

Expand|Select|Wrap|Line Numbers
  1. DoCmd.TransferSpreadsheet acExport, , "qryQueryName", "FileName", True, "SheetName" & Counter
Hope this helps!
Aug 21 '12 #4

Expert Mod 10K+
P: 12,365
It wasn't stated outright, but the implication is that there is no native functionality to do what you want to do. It will have to be created.
Aug 21 '12 #5

Post your reply

Sign in to post your reply or Sign up for a free account.