Adding Blank Excel worksheet to Exported report? Can it be done from Access 2007?

[casinc815]

We have an application, in Access 2007, that assists teachers in managing the mountain of assessment/test
data that they accumulate each week. We have created Access reports to inspect and review the data and


on each report we have several buttons at the top right corner of the report. One button is to exit the report, another prints, another saves PDF and finally, one button exports the report to Excel.

Now that the IT team has let the jeannie out o the bottle, the teachers are embracing the technology and
actually save time and effort in creating their own reports.

The issue has now come down to automating the export a bit further. Mind you, all works well. The report
generation in Access and the export to Excel work well. Now, the request is to add blank worksheets to the
existing export XLS, once it has been created. In other words, export and then add 3 blank worksheets.

One of our more ambitious teachers stopped by, to ask if it were possible to automate the addition of blank worksheets to an existing export task?

At the bottom of a worksheet, one sees Sheet1 or the name of the worksheet. The teacher would like
to have Sheet2, Sheet 3, ...etc added. Yes! She can do it manually, but wanted to know if it can be
automated. The result of a saved export is only one worksheet on exporting.

Your thoughts would be appreciated? Remember this is an exported XLS file and there is nothing on the
worksheet except the data from the report.

Thanks!

casinc815

[ranman256]

I would say export an empty query with 1 field but no field headers.

vFile = "c:\myfile.xls"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qsMyQry", vFile, false, "Sheet1"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qsMyQry", vFile, false, "Sheet2"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qsMyQry", vFile, false, "Sheet3"

[casinc815]

Interesting! Thank you for the quick response.

Will this work if the worksheet name at the bottom is a file name created by Access at the time of exporting?
So in the workbook, at the bottom of the first worksheet, one would see filename, sheet1, sheet2,sheet3! Is
that correct?

I am writing the code as we converse.

Thanks!

[ranman256]

yes, i omitted your 1st query...
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qsMainQry", vFile, true, "TabName"

As long as you used the same Filename (vFile), and different tabs, they will all go in the same report.

[casinc815]

yes, i omitted your 1st query...
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qsMainQry", vFile, true, "TabName"

As long as you used the same Filename (vFile), and different tabs, they will all go in the same report.

Thanks! I will let you know how it works out.

Jim