Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

MS Access - exporting to excel

Status
Not open for further replies.

sweetleaf

Programmer
Jan 16, 2001
439
CA
Hi,

Does anyone know how to extract table data to excel when there are more than 65k rows (I think 65k is the limit to how many rows you can add in an excel worksheet)?

I know that one alternative is to drag and highlight which rows but I have nearly 300k rows and doing it this way would take far too long.

Thank you for any advice.
 
Excel 2007 or 2010 would give you more than enough room.

Older versions of Excel were restricted to 65536 rows per worksheet. In Excel 2007 this restriction - the number of rows is larger by about 16 times - is now 1,048,576 rows.


Lyle
----
Insanity - a perfectly rational adjustment to an insane world. -RD Laing, (1927-1989)

 
Programmatically - after DoCmd.TransferSpreadsheet use undocumented '10' for 'xlsx' format.

combo
 
Thanks!

Unfortunately I am stuck with the version of Excel that I have...I'll give the docmd approach a try.

Combo, what event would I attach this, to trigger it etc...?
 
Add module and write procedure. The beginning of code inside:
[tt]DoCmd.TransferSpreadsheet acExport, 10,[/tt]
See vba help for the rest of parameters.
Next you can assign this cde to access macro, button on the form, switchboard item, or run directly from the vbe.

combo
 

Slightly long winded but write queries over the table selecting on row number from 1-65,000 then 65,001 - 130,000 etc then export the result sets.

I love deadlines. I like the whooshing sound they make as they fly by
Douglas Adams
(1952-2001)
 



Hi,

In Excel 2003, I have done thousands of queries of tables with millions of rows of data, but I have seldom returned more than 65,000 rows. You most often do not need to return tnat many rows to a spreadsheet.

What are you REALLY trying to accomplish?

Skip,
[sub]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
Without any VBA:

In Excel (not Access),
Go to Data > Get External Data > Access
Browse to find the file
Select the Table you're after

I've brought in several few hundred thousand records in one shot.

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top