• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 5836
  • Last Modified:

MS Access export multiple queries to separate worksheets in Excel workbook

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
0
iainmacleod
Asked:
iainmacleod
1 Solution
 
Rgonzo1971Commented:
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
0
 
iainmacleodAuthor Commented:
Hah, easy when you know how....!  Thanks very much..
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now