Export query, changing one criteria multiple times, to Excel

[Guest]

I have a two part question. I am exporting a query to an Excel spreadsheet
every week with each tab containing query results with one criteria changed.
I have 24 different results for different areas. I originally set up
multiple queries in Access, each with the one changed criteria, and then
exported them using the DoCmd.TransferSpreadsheet.

'DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"qry_CTA_Cincinnati", "D:\ CTA Details.xls", , "Cincinnati"

This was wonderful until we started modifying the base query which meant I
had to go back and change each individual query.

My question is how to write a loop in VBA that will take the original query,
add the criteria, export it to Excel, then take the next criteria and do the
same thing till it is done. That way if there are changes to the original
query I will only have to change it once.

 

[Alex Dybenko]

Hi,
you can build a query SQL based on original query. like:

currentdb.querydefs("MyQuery").SQL="Select * from BaseQuery Where
State='WA'"

and then export it

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "MyQuery", "D:\
CTA Details.xls", , "Cincinnati"

so if you have state names in a table or array - then you can loop through
and run these 2 lines for each state

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

 

[Guest]

Alex,

I tried your method but am getting an error "Item not found in this
collection". My code is

CurrentDb.QueryDefs("MyQuery").SQL="Select * from qry_Details Where Area =
'Cincinnati'"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "MyQuery", "D:\
CTA Details.xls", , "Cincinnati"

I amde the assumption that "MyQuery" is the name of the new query and the
actual query name I substituted for BaseQuery in your sample. I hope this is
correct. Any ideas what may be causing the error?

 

[Alex Dybenko]

Hi,
first you have to create a new query MyQuery and save it, perhaps this is
what cause the error

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

 

[Guest]

That was it. I have combined that with a For/Next statement and it does
exactly what I needed.

Thank you so much,

Jacki8