Access export to multiple sheets in Excel
 

Every month I'd like to export a large table (200,000 rows, 55 fields)
from an Access 97 database to a series of about 30 Excel workbooks with
10 sheets in each workbook. Every row in the table will go into one of
the worksheets. New workbooks will be created every month. Obviously
I'd like this to be as automatic as possible.

The data will be split between workbooks based on fields called
"Company" and "Product". The split between sheets within the workbook
will be based on 10 "Regions". However, some combinations of these
variables do not exist (and this changes monthly) and I would prefer
that blank worksheets aren't created.

I am thinking of using the OutPutTo command in VBA. From what I can
tell if I want to export to a worksheet with a specific name the query
(or table) from which I export has to have that name. If so do I need
to use CreateQueryDef to create a new query with the correct name and
criteria in it or is there a better solution?

Assuming I use the approach outlined above I wasn't sure how to get
Access to know exactly which of the 300 potential queries need to be
run. I can easily create a query that will contain one row for each of
the worksheets that I want to create but wasn't sure make use of this
in the query

Thanks for any help and sorry if I've been unclear!

Jeff