Solved

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

Posted on 2003-10-22
6
594 Views
Last Modified: 2008-03-03
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!
0
Comment
Question by:eldo64
6 Comments
 
LVL 11

Expert Comment

by:BillPowell
ID: 9601388
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
 
LVL 54

Accepted Solution

by:
nico5038 earned 500 total points
ID: 9601430
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
 

Expert Comment

by:Icart
ID: 9601480
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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
LVL 6

Expert Comment

by:DoppyNL
ID: 9604659
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
 
LVL 6

Expert Comment

by:DoppyNL
ID: 9604673
just to complete:

You don't have to specify the worksheet, it will be sent to the sheet with the query name everytime.
0
 

Author Comment

by:eldo64
ID: 9610332
Thanks Nic;o) for your help in building the VBA module to accomplish my automation task!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

679 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question