Other than having some sort of Print-To-Excel printer driver, I'm not sure
you can export the formatted report, per se. However, you could at least
export the same data by applying the TransferSpreadsheet Method to the
table/query underlying your report.
What I would do is to put two buttons on the form from which you run the
report. Have one button open the report and have the other export the
contents of the underlying query to Excel using the TransferSpreadsheet
Method:
docmd.TransferSpreadsheet
acExport,acSpreadsheetTypeExcel9,"TableNameGoesHere","FilePathAndNameGoesHere" (that is all one line)
If you want to get header information onto the spreadsheet, try this:
1. Exporting the header information to one spreadsheet (let's call that
HeaderSpreadsheet)
2. Export the details to another(let's call that DetailSpreadsheet)
3. Open a third spreadsheet (from Access using the Shell method) that
contains an Auto_Open macro that opens HeaderSpreadsheet, opens
DetailSpreadsheet, copies the contents of DetailSpreadsheet into
HeaderSpreadsheet below the header information, and finally saves
HeaderSpreadsheet replete with details.
Someone else may have an easier way to do this.
