Export multiple queries to Excel

[pperilman]

I have an access DB with over 30 quarterly reports that are run and sent out to different people at the company. each query is setup eaxactly the same way with slightly different parameters. There is also a prompt for starting transaction date and ending transaction date for each query.

Is there a way to set up either a macro, VB script or query that can export all of the queries with one action and only having to enter the date parameters once?

 

[lameid]

Use a form as the source of your parameters in your queries and kick of your process from the click event of a button on the form that dumps the queries to excel.

Transferspreadsheet method of the docmd object will export to a spreadsheet (or import one depending on the parameters used).

 

[pperilman]

Thank you for the quick response. i am new to VB and creating this type of thing in Access. Can you give me an example or explain further what you mean by use a form as the source of your parameters?

Right now I do not use forms. Only I and others in my department use the DB and just open the queries straight from the main DB window.

 

[randy700]

Create a form and, at a minimum, add two text boxes and a command button. Name the text boxes txtStart and txtEnd.
Name the command button cmdReports. Place a meaningful caption on the button. Name the form frmMain.

In your query, replace <Enter Start Date> with =Forms!frmMain.txtStart. Replace <Enter End Date>
with =Forms!frmMain.txtEnd.

With your form in design view, right click the button and select Properties. In the properties box, select
the Event tab. Click in the space next to On Click, select the elipses (3 dots) that appear at the right.

When the code window opens, enter the code suggested by lameid.

Randy

 

[lameid]

Thanks for answering Randy... Sometimes my tek-tips e-mail notifications are really slow.

Although more exactly...

In your query, replace [Enter Start Date] with =Forms!frmMain!txtStart. Replace [Enter End Date] with =Forms!frmMain!txtEnd.

The dot instead of explanation point would probably work but parameters use square brackets.

You should also then right click the grey area in the query window and select paramters. Add the same references to the controls there and specify the datatype of Date/Time for each. You should do for all parameters except for text because of an Access bug.

 

[pperilman]

Ok I finally got around to setting this up and I am having trouble with the DoCmd.TransferSpreadsheet command. I want it to grab the dates entered on the form and export all of the queries starting with the word "Quarterly". I would also like the name of the exported file to be the same as the query name.

Do i need a loop to get this done? If so which loop and how do I pass a variable into the DoCmd function to have it run each query.

 

[lameid]

Do i need a loop to get this done?

No, you could literally run the statements repeatedly for each query. You could also put the queries in a table or even the querydefs collection.

If so which loop and how do I pass a variable into the DoCmd function to have it run each query.
This would depend on the methodology. If using a table you would use a recordset and a while or do loop. If using the querydefs collection, you would use a For/Next loop.

In any case you can use a string or anything that returns one as a parameter that takes text.

So if qry is a querydef then your filename might be...

"C:\temp\" & qry.name & ".xls"

That would save the file to temp on C:

 

[pperilman]

I am not sure this solves my problem. I am trying to run all of the queries using the same date parameters with one button click. I would also be easier if I could just run ti for all queries whose name started with Quarterly so that if queries are added I don't have to go through and manually add them in order to have them be run.

 

[lameid]

I don't know the transferspreadsheet parameters cold but this is the basic structure...

Dim qrys as DAO.Querydefs 'You need a reference to DAO
Dim qry as DAO.Querydef

set qrys = currentdb.querydefs 'skip database variable / only needed once

For Each qry In qrys
     If Left(qry.name,9) = "Quarterly" Then
           'docmd.transferspreadsheet goes here
     End if
Next
set qry = nothing
set qrys = nothing