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

Export Access table to excel

P: n/a
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
Nov 13 '05 #1
Share this Question
Share on Google+
7 Replies


P: n/a
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.

"Keon" <ja*********@telenet.be> wrote in message
news:Qe*****************@phobos.telenet-ops.be...
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

Nov 13 '05 #2

P: n/a
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??????
"Keon" <ja*********@telenet.be> wrote in message news:<Qe*****************@phobos.telenet-ops.be>...
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

Nov 13 '05 #3

P: n/a
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.

"Keon" <ja*********@telenet.be> wrote in message
news:Qe*****************@phobos.telenet-ops.be...
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

Nov 13 '05 #4

P: n/a
OutputTo has more severe limitations than TransferSpreadsheet. 16,384
records per table (or thereabouts).

--

Ken Snell
<MS ACCESS MVP>

"MissiMaths" <mi********@hotmail.com> wrote in message
news:75**************************@posting.google.c om...
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??????
"Keon" <ja*********@telenet.be> wrote in message

news:<Qe*****************@phobos.telenet-ops.be>...
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

Nov 13 '05 #5

P: n/a
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
<MS ACCESS MVP>

"Keon" <ja*********@telenet.be> wrote in message
news:Qe*****************@phobos.telenet-ops.be...
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

Nov 13 '05 #6

P: n/a
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.

"Keon" <ja*********@telenet.be> wrote in message
news:Qe*****************@phobos.telenet-ops.be...
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

Nov 13 '05 #7

P: n/a
On Sun, 28 Nov 2004 11:19:54 GMT, "Vern" <vh*****@mchsi.com> wrote:

Try making a query that includes everything in the table. Then export the
query to excel. You should get all the records, but some of the data in some
some of the fields may still be a problem. (a text field containing only
numbers will be changed to a number field in Excel and any leading zeroes will
be dropped. It's been a while, but I think there are other probles. They are
not insumountable if you just have to have the date in Excel.)

Chuck
....
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.

"Keon" <ja*********@telenet.be> wrote in message
news:Qe*****************@phobos.telenet-ops.be...
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


Nov 13 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.