MS Access export multiple queries to separate worksheets in Excel workbook

iainmacleod
iainmacleod used Ask the Experts™
on
I am currently using docmd.output to and exporting various queries in to separate Excel workbooks. Ideally I would like to export these queries to separate worksheets within one workbook and name the worksheets with the same name as the query.

This is what I do currently.

DoCmd.OutputTo acOutputQuery, "Bens CC", "Excel97-Excel2003Workbook(*.xls)", "z:\mi\BensCC.xls", False, "", , acExportQualityScreen
    DoCmd.OutputTo acOutputQuery, "Cab CC", "Excel97-Excel2003Workbook(*.xls)", "Z:\MI\CABCC.XLS", False, "", , acExportQualityScreen

Thanks for any advice
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2016
Commented:
Hi,


pls try

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Bens CC",  _
"z:\mi\YourFile.xls", True, "Bens CC"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Cab CC",  _
"z:\mi\YourFile.xls", True, "Cab CC"

Open in new window

Regards

Author

Commented:
Hah, easy when you know how....!  Thanks very much..

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial