• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2151
  • Last Modified:

Export an Access Report to Excel by passing overflow error (either can be done or it can't)

hi there,
I have an access report that its 7682 pages is there a way to export it to excel? i have tried before but get overflow error after 1209 pages. is there another way of doing this?
Thanks,
jsctechy
0
jsctechy
Asked:
jsctechy
5 Solutions
 
Rey Obrero (Capricorn1)Commented:
excel has a limit of 65000 + rows.
you can divide your reports record source into several queries and export the queries to different excel files.

is this acceptable?
0
 
Patrick MatthewsCommented:
Hello jsctechy,

If you are running Excel 2003 or earlier, you are limited to 65,536 rows in a worksheet, so if your
Access report needs almost 8000 pages, it does not look like good odds.

Regards,

Patrick
0
 
Patrick MatthewsCommented:
jsctechy,

BTW, Excel 2007 boosts to 1,048,576 rows.  Of course, I have no idea whether Access's
routine for the export can exploit this new capability or not...

Regards,

Patrick
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>I have an access report that its 7682 pages is there a way to export it to excel?
Very bad idea.  Access Reports do not export to other editable formats very well, and often resemble a Picasso of their original cosmetic formatting.
Either export the query that serves as your report's RecordSource to Excel, or text if you go up against the maximum rows limit as the above experts stated, or write to a pdf or snapshot file.
0
 
jsctechyAuthor Commented:
all your suggestion sound good but the need to export to excel is inmiment. They need to calculate and add however they want so they need it in excel format.
i like the idea of divide the query in different queries and have multiple files i think thats the best thing i can do.
Thanks,
jsctechy
0
 
stevbeCommented:
jsctechy,
   Have you tried exporting the query the report is based on? How many records are displayed if you just run the query ... if less than 65,536 then that would be the course of action to persue. This will likely get them better results to work with (Access does a bad job of exporting reports into Excel) anyway, no umping columns, funny column headers etc. You can control the headers that will be put into Excel by aliasing the filed names in the query itself.
SELECT MyFiled As FieldOne

<Of course, I have no idea whether Access's routine for the export can exploit this new capability or not...>
nope ... 2007 can not export a report to Excel AT ALL.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now