Export Access table to excel

[Keon]

Hoi,

I'm using a database with alot of records in 1 table (more than 3000). If i
want to export this table to excel i only get it till record 2385. Do
someone know how i can solve this probleme?
To export my tables i use folowing code:

StTotaal = stLocatieName & "\Archief\" & Year(Date) & Month(Date) &
Day(Date) & "_Gegevens.xls" 'location and name where the table must be
saved
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "tblBills",
StTotaal, 1, , 1 'exporting of the table tblBills

Thanks

Greetings
Koen

 

[Vern]

Excel can also be limited by the number of columns, I have had problems when
exporting tables with high column count.
Also try to just use a macro to do the export and see if it works. The
convert the macro to VB and use that code if it works.

 

[Guest]

I am having this same problem and only using 4 columns and running it with a
macro.
It will not export saying "There are to many rows to output, based on
limitation specified by the output format or by Microsoft Access."

 

[MissiMaths]

Someone may post that excel can only accept so much data! This is
probably the case here. However, whenever I'm exporting tables from
access to excel, I use outputTo instead of the transfer command! this
may allow you transfer more??????

 

[MacDermott]

I'd suggest you look carefully at record 2385 of your data.
Is there something in this row which might not export correctly?

I've had data exports to Excel (albeit this was from a query) stop at a
record where I had a value of #Error in one column.

AFAIK, Excel spreadsheets are limited to 64,000 rows, so this is unlikely to
be the problem.

HTH
- Turtle

BTW - that's an awful lot of groups you're posting to. This question has
nothing to do with toolkitode or setupconfig. Or macros, for that matter.

 

[Ken Snell [MVP]]

For Excel9, the limitation is 65536 records per export and 255 fields per
record being exported.

Your description of the table suggests that this is not the limitation that
you're getting.

Try compacting and repairing the database and then try it again. There may
be a problem with your table.

 

[Ken Snell [MVP]]

OutputTo has more severe limitations than TransferSpreadsheet. 16,384
records per table (or thereabouts).

 

[Vern]

Another problem may be the field type. Is one of the fields an OLE type.
Excel may be wanting you to stick with numbers, text and dates. Also Access
has a variety of number types, not sure if Excel can accept all of them.