How to export multiple Access queries into specific worksheets in one Excel workbook?

I have an Access database that is updated weekly with information about 12 different locations.  Within the database, 4 queries for each location (48 total) are used to organize information for trending.  I've created an Excel spreadsheet for each of the 12 locations that uses the output from these 4 queries on 4 individual worksheets within the spreadsheet to trend the information on graphs.  Since I need to update all 12 separate Excel spreadsheets weekly, I'd like to automate the process.   Is there a way to automatically export each of the 4 queries per location into their respective spreadsheets on the correct worksheet?  I've tried to use the TransferSpreadsheet option within an Access Macro, but that doesn't work because the Excel file already exists and you can't specify a specific worksheet.   I've also tried some simple VBA (which I'm not proficient at), but didn't get that to work either.   Basically I need a way to overwrite an existing worksheet with new updated data from an Access query so the trending graphs can be updated.  Thanks for the help!
eldo64Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

BillPowellCommented:
Not a complete answer, but something to help you along.  I would link these spreadsheets to show up as tables in access.  Then I would perform append and update queries on them completely through access.  You could even set up a procedure that runs all of these queries all at once with the click of a button.

Bill
0
nico5038Commented:
In a similar case I've created an excel workbook with a sheet per graph.

For each sheet the data is moved from a query into the needed cells.

Perhaps it's best not to overwrite the excel workbook(s) but to add the date (e.g. Year & Weeknumber) to the filename to be able to tell what data is in the workbook "from the outside".

When you drop a sample spreadsheet and the .mdb with some data and one query in my nico5038 mailbox "at" yahoo.com I'll make one module for you as a sample. And publish the code here.

Nic;o)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
IcartCommented:
You may want to use the Data, Get External Data - New Database query in Excel.  Copy the query (SQL)  from access into the spreadsheet (One at at time ;-( ). every time you open the Excel file you can refresh the data, which re runs the query.. This will update the data source automatically..  
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

DoppyNLCommented:
Use TransferSpreadSheet to transfer the query's once to a new xls.
You will notice that the worksheets have the name of your query's!

Might also work to rename your worksheets to the names of your querys.

using transferspreadsheet again will overwrite the data currently in the spreadsheet if I'm correct.
0
DoppyNLCommented:
just to complete:

You don't have to specify the worksheet, it will be sent to the sheet with the query name everytime.
0
eldo64Author Commented:
Thanks Nic;o) for your help in building the VBA module to accomplish my automation task!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.