asked on

How do I export multiple Access queries into different worksheets on one Excel file

I have several queries in an Access 2007 database that I would like to export into one Excel file with multiple worksheets. (e.g. Queries pull tables for OH, MI, and IN, I would like one Excel file called "District.xls" with three worksheets for OH, MI, and IN.) I would also like to keep the formatting intact when the files export (so the headers look pretty, bold, grey background, etc.)

My import and data scrubbing module is in the Access file, so I would prefer to stay in Access if possible. Otherwise, if I need to move to Excel to do this, I am not too terribly attached to doing it in Access.

I am very comfortable with VBA, but my google searches have not produced a "silver bullet" per se. It's mostly sharing worksheets, linking tables, and the like. So, 350 points to the person who can get me what I need, please.

ASKER CERTIFIED SOLUTION

GRayL

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

shilhan

You can use the VB script to run multiple queries . An example of VB script which you can use for multiple queries can be as shown below :

Make a form, and add 3 objects to it, two unbound text boxes(not labels), and one command button. Cancel the wizard that pops up when you add the command button. Lets call the text boxes txtStart and txtEnd. You might want to change the format of the text boxes to 'Short Date' just to be safe. (This can be found in the properties window.)

2) Save the form, lets call it frmRunReport.

3) Change the Criteria in ALL nine of your queries to reference the fields in the form, instead of the dates that are there:

06/05/2006 becomes Forms![frmRunReport]![txtStart]
06/30/2006 becomes Forms![frmRunReport]![txtEnd]

4) In design view of the new form, right click the button, go to properties, events, and click the 'build' button next to OnClick (looks like an ellipses (...)).

Now you have to write some code You can even paste the following between the lines that start with Sub and End. Just be sure to change the names of the queries and report to match the ones in your database:

''Start Code

DoCmd.SetWarnings False
DoCmd.OpenQuery "qryFirstQuery"
DoCmd.OpenQuery "qrySecondQuery"
DoCmd.OpenQuery "qryThirdQuery"
DoCmd.OpenQuery "qryFourthQuery"
DoCmd.OpenQuery "qryFifthQuery"
DoCmd.OpenQuery "qrySixthQuery"
DoCmd.OpenQuery "qrySeventhQuery"
DoCmd.OpenQuery "qryEighthQuery"
DoCmd.OpenQuery "qryNinthQuery"
DoCmd.OpenReport "ReportName"
DoCmd.SetWarnings True

''End Code

ERNesbitt

ASKER

I was hoping there was a way with 'DoCmd.OutputTo acOutputQuery' because it keeps the table formatting when it exports.

Markus Fischer

If you are familiar with Excel, you can automate the export to individual files (DoCmd.OutputTo), and the assembly into one single workbook (using the worksheet's Move method).

(°v°)

ERNesbitt

ASKER

I was hoping not to need additional scripting to format the tables after export, but I guess that's just a limitation of access.

GRayL

Thanks, glad to help.