Go From Docmd.openreport With Wherecondition To Docmd.transferspreadsheet, Access 201
I have searched the forums and found some scenarios that seem similar to mine but I can't see how to apply them to my situation.
My forum search was "WhereCondition for DoCmd.TransferSpreadsheet"
I have code that calls the DoCmd.OpenReport in a loop passing different a different WhereCondition each time through the loop.
DoCmd.OpenReport str_ReportName, acViewPreview, , MyWhereCondition
This generates a nice collection of reports customized by my WhereCondition.
I now want to generate an Excel spreadsheet for each report as I loop through the long list of WhereCondition values.
Very unfortunately the DoCmd.TransferSpreadsheet does not have the ability for the WhereCondition that the DoCmd.OpenReport has.
This is very clearly stated on the MS site.
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, TargetQuery, PathFileName
I'm looking for a simple way to create the Excel file from the loop where I call my DoCmd.OpenReport call.
One way I can think of to accomplish my task is to
--- get the current report's query (it is not built on the fly, it is in the list of Queries)
--- modify the Where clause to include my WhereCondition
--- Execute this modified query.
I've had no success with the above as I've only found code that seems to permanently alter the underlying query and even those attempts have not run cleanly for me
My idea seems simple in theory but I'm finding it difficult to figure out how to do it. Perhaps there is an alternative approach?
I find it hard to believe I'm the first person who needed to do this.