Exporting a Dynamic Query w/ VBA

  • Thread starter Thread starter Meek
  • Start date Start date
M

Meek

I'm looking to create a macro/code to run a query, export
the data to an Excel file, increment the query criteria
automatically, run the updated query, export the new
results to a new excel file, etc. until the end of the
incrementations. Please bear with me as I rarely work with
VBA and this is my first Access venture.

It comes down to I'm not sure how to edit or add queries
from VBA. I was planning a For loop, corresponding to an
array containing the strings I want to update the query to
use in each sucessive increment.

For simplicity I'll include a part of my psuedocode,
the "QUERYX =" part is where I fall short. Currently I
have how the SQL WHERE would be done w/ array variables.

For intCounterA = 1 to 3
QUERYX = WHERE `GPH 2001`.PER = astrPerCounter
(intCounterA) AND `GPH 2001`.EDATE >= alngStartDate
(intCounterA) AND `GPH 2001`.EDATE <= alngEndDate
(intCounterA)

strFileName = "SpreadSheet" + str(intCounterA) + ".xls"
DoCmd.TransferSpreadsheet acExport, 8, QUERYX, strFileName
next

If need be I'll settle for creating a whole new query in
VBA running it to export the data and then deleteing it,
only to repeat the process for each query I need to run.

Any help for a young fool like me would be greatly
appreciated.
 
Back
Top