Output to Excel

[Arry]

Hello all,

Hope you can help.

Does anyone know a way to export data from an Access table/query into an excel file??

Sounds straight forward but what if the table/query is over 65,536 lines. Is there an easy way to split the records and paste into different excel sheets.

For example 70,000 lines in a query, output to excel to create one sheet with 65,000 and another sheet in the same workbook with the remainder?

All Help or possible suggestions VB, SQL etc... gratefully received!

Cheers.

Arry

 

[fuzzygeek]

Export to Excel

All you would have to do is count the records as they are placed in the first spreadsheet. Open the second , format it and start placing the data in it. Attached is sample code, not doing exactly what you want, but exporting data to two spreadsheets. I set up tables (not included) that have fixed data for inclusion for formatting, titles, etc. The regular variable data is then fed into the spreadsheet. Good Luck

 

[Arry]

Many Thanks

I will give it a go!

Arry

 

[Arry]

Any other suggestions anyone??

Is there an easier way?

 

[The_Doc_Man]

Well, try two queries? If there is any field that can be used as a primary key then you can find the key value in a couple of complex steps.

First, build a query that contains the first half of the table. You need to use DCount to determine that number. This is a Find First type query.

Next, find the key value for the last record in that "first half" query. Use a DMax to find that key.

Third, build a second query that contains all records from the table where the primary key is greater (but not equal to) the key you fond.

Optionally, in case the only key is duplicable, build a third query that contains all records from the table where the key is less or equal to the key you found.

You would, of course, only export two queries. The third query is always the second query you export. The first query you export is either the first or the optional one I defined.

This would give you the closest approach to an even split between two tables as long as the key is either unique or has only a few duplications.