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