How to I export to multiple Excel Sheets in Access

[Macro Newbie]

I posted a reply to an old question and am unsure if I should have just
posted a new question so here it is:

I would like to also export a databse to multiple worksheets in a single
workbook but have not been able to accomplish it using the method in this
post.

My situation is that the Access table I have has 100000+ records and I would
like to export to excel having the rows overflow to a new worksheet as each
worksheet is filled to its row limit. I am a novice macro user so as much
detail as possible would be appreciated.

Thanks in advance....

 

[Kernow Girl]

Hi Newbie -

(the text in quotes is from the Help File for TransferSpreadsheet Action)

Create several queries, breaking the data into chunks that will fit onto a
page in the spreadsheet by say in the criteria for the first query Between 0
And 10000, in the Second Query Between 10001 And 20000, etc , etc.

Set up a Macro with a line for each query or 'chunk' of data, and in the
Table name put the Query Name --- "The name of the Access table to import
spreadsheet data to, export spreadsheet data from, or link spreadsheet data
to. You can also type the name of the Access select query you want to export
data from. This is a required argument."

On the File Name line put - "...... export to, ..... Include the full
path. This is a required argument. Access creates a new spreadsheet when you
export data from Access. If the file name is the same as the name of an
existing spreadsheet, Access replaces the existing spreadsheet, unless you're
exporting to an Excel version 5.0 or later workbook. In that case, Access
copies the exported data to the next available new worksheet in the workbook.

Attach the Macro to a button or some such.

Hope this helps - yours Dika