Export to workbook by Query Criteria

[Guest]

I search around for qutie sometime and still cannot find a solution to this
problem.

I have a db that runs query reports with month and year as criteria and a
number of excel workbooks named "Report MMYY". What I want is a code that
will automatically run the querys using the criteria (I've got code for this
part alread), then the code will search through a particular folder location
for the respective report correspond to the month & year criteria, if the
workbook exist, it will transfer the result to the spreadsheet, if the
workbook does not exist, the system will create a new report using the
"Report template" in the drive and name it "Report MMYY". Is this possible?

I'm currently using earling blinding to run the criteria and transfer file
to excel.

Thanks in advance.

 

[John Nurick]

Hi Sin,

You can use the Dir() function to discover if the workbook already
exists.

When you say "transfer the result to the spreadsheet", I presume you
mean creating a new sheet in the workbook containing the data from the
report. Otherwise you could simply delete the existing workbook and
let the export create a new one.

See http://accessblog.net/2006/07/export-to-excel-range.html for
information on how to control the name of the new sheet that is
created.