Create multi worksheet excel workbook from access

Posted on 2011-04-20
Last Modified: 2012-08-14
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
Question by:johnnyg123
    LVL 119

    Accepted Solution


    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)
    LVL 9

    Expert Comment

    DoCmd.TransferSpreadsheet {Direction of Transfer}, _
        {SpreadSheet Type}, {Query to Select Data}, _
        {Full Name of Excel WorkBook, True, {Name of WorkSheet}

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    The first two articles in this short series — Using a Criteria Form to Filter Records ( and Building a Custom Filter ( — discuss in some detail how a form can be…
    Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
    The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
    This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now