Solved

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

Posted on 2003-10-22
6
559 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 6

Expert Comment

by:DoppyNL
Comment Utility
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
Comment Utility
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
Comment Utility
Thanks Nic;o) for your help in building the VBA module to accomplish my automation task!
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
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 …

772 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now