Access Reports - Same Workbook, Difference Worksheets

[TheEntertainer]

I'm using Access 2000 and Excel 2000.

I have created 3 reports these are listing, sub-summary, summary reports, these filter on a couple of fields (class and year) and I've sent up a loop to produce the required report per class and year.

What ideally I would like to do is as the report is going through the loop to export the claims listing first and then the sub-summary and summary reports to the same Workbook but a different worksheet.

I've had a search on google/tek-tips to see if I could find something on this and came across thread703-724042, but the answer recommended using the Transferspreadsheet option, but this only works with tables not reports.

Does anyone know if this is possible in code?

Thanks

 

[hneal98]

You can not do that from a report. You would need to do that from a query first. You can create the queries and then use the Transferspreadsheet as stated in the article. You would have to create a button on your form that would do this before or after your report prints.

 

[TheEntertainer]

Thanks hneal98,

I figured that was going to be the response. I'm now thinking along one of two lines either.

1) Do I actually need reports? loop the queries and output

or

2) In Excel pick up the file names and copy the related tabs across as the related reports claim listing, sub-summary and summary will all start with the same Class and Year information.

Cheers