Solved

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

Posted on 2003-10-22
6
600 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Technology Partners: 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!

 
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

Technology Partners: 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

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

707 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