Export Access table to Excel across multiple tabs

[Harry Haecker via AccessMonster.com]

Hello All:

I have one table which has two primary keys: Location and Item.
I also have a lookup table which has only one field [Location] and just
lists the top ten Locations for that week

I need to automatically export this table to Excel across multiple tabs (10
in all), using [Location] as the worksheet name.

Table name = RTVInt
lookup table name = OverInventoryStores
PrimaryKeys = FacilityNumber, Item
Output workbook = "C:\RTV.xls"
Worksheet name = [Location]

I did look for other posts along this line and found a recent (3/17/05)
solution to a very similar problem (Nikos Yannacopoulos/Steven Britton)
"Export to Excel making Multiple Tabs"), but after spending hours
troubleshooting I was unable to make it work.

I know that, theoretically, what has to happen is that a loop is set up
which takes each of the [Location] records in OverInventoryStores (one at a
time), and places results in a query which is then exported to an Excel
file with [Location] as the tab name.

Thank you for any help you all can give.

Sincerely,

Harry Haecker
Data Analyst
harryh at tomasjewelry dot com[mercial]

 

[Harry Haecker via AccessMonster.com]

Correction:
PrimaryKeys = Location, Item