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

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

syedshaffee
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


zmbd
Expert Mod 5K+
P: 5,287
There is no way to exceed the maximum number of rows in a single data sheet within an excel 2003 workbook. http://office.microsoft.com/en-us/ex...005199291.aspx

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

syedshaffee
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

twinnyfo
Expert Mod 2.5K+
P: 3,054
syedshaffee,

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
  2.  
Hope this helps!
Aug 21 '12 #4

Rabbit
Expert Mod 10K+
P: 12,315
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.