Create multi worksheet excel workbook from access

I have an access application that I need to modify that will allow 2 access tables to be saved into a single excel workbook.  (one worksheet will contain one table and the other worksheet will contain the other table.

I have used transferspreadsheet to export 1 table to excel but not sure if I can use it to create 2 worksheets in the same workbook

Also, I would like to be able to prompt for the file location to save the workbook
Who is Participating?
Rey Obrero (Capricorn1)Commented:

you can do this

docmd.transferspreadsheet acexport,8, "table1","c:\folder\myexcel.xls", true,"Table1"

docmd.transferspreadsheet acexport,8, "table2","c:\folder\myexcel.xls", true,"Table2"

you will have two sheets with the name of the tables (table1 and table2, in this case)
DoCmd.TransferSpreadsheet {Direction of Transfer}, _
    {SpreadSheet Type}, {Query to Select Data}, _
    {Full Name of Excel WorkBook, True, {Name of WorkSheet}
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.