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

Limitations in exporting to Excel?

P: n/a
How do I remove the limitation in Access that deny me from exporting 24000
rows and 17 columns (in a query) into Excel?

Kenneth
Nov 13 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
"Salad" <oi*@vinegar.com> wrote in message
news:po*****************@newsread2.news.pas.earthl ink.net...
Kenneth wrote:
How do I remove the limitation in Access that deny me from exporting 24000 rows and 17 columns (in a query) into Excel?

Kenneth

The specifications are Maximum worksheet size 65,536 rows by 256 columns
in Excel. So you fit under that requirement.

Do you get an error? How are you exporting it? How may rows do you
have in the query. Please provide more, and relevent, information.


Here is what I have done:
In the Datasheet view:
File - Export
Save as type: Microsoft Excel 97-2000
[x] save formatted
Export

Here is the error message:
"There are too many rows to output, based on the limitation specified by teh
output format or by Microsoft Accsess"

....I only have 24000 rows and 17 columns, what is wrong?
(I manage to export if I choose to save as Text file, and then open the Text
file in excel, but why can't I export directly to excel?)
Nov 13 '05 #2

P: n/a
Kenneth wrote:
"Salad" <oi*@vinegar.com> wrote in message
news:po*****************@newsread2.news.pas.earthl ink.net...
Kenneth wrote:

How do I remove the limitation in Access that deny me from exporting
24000
rows and 17 columns (in a query) into Excel?

Kenneth


The specifications are Maximum worksheet size 65,536 rows by 256 columns
in Excel. So you fit under that requirement.

Do you get an error? How are you exporting it? How may rows do you
have in the query. Please provide more, and relevent, information.

Here is what I have done:
In the Datasheet view:
File - Export
Save as type: Microsoft Excel 97-2000
[x] save formatted
Export

Here is the error message:
"There are too many rows to output, based on the limitation specified by teh
output format or by Microsoft Accsess"

...I only have 24000 rows and 17 columns, what is wrong?
(I manage to export if I choose to save as Text file, and then open the Text
file in excel, but why can't I export directly to excel?)


It appears this is not a problem of exceeding Excels limits but that of
exceeding Access's limits.

Try the following. Click on Modules/New and paste this code into it.
Change the words YourQUeryName to the query name you use and run it
(press F5) or Run/GoContinue.

See if it blows up. If so, run a couple of other tests. Create a query
that contains just the ID of the field. Run it. Does it work? If not,
remove the id and try another field and run. DOes it work? IF not,
remove that field and try again. If ANY of these works, then start
adding columns 1 at a time until it fails. When it does, that field may
be causing problems in the export.

But if this is an Access export limitation, you'll be better off with
your current solution of exporting to text.

Sub ExportToExcelTest()
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, _
"YourQueryName", "C:\Test.XLS", True
MsgBox "Done"
End Sub

Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.