Solved

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

Posted on 2003-10-22
6
570 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

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…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…

920 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

16 Experts available now in Live!

Get 1:1 Help Now