How to export multiple Access queries into specific worksheets in one Excel workbook?
Posted on 2003-10-22
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!