DoCmd.TransferSpreadsheet export

Posted on 2011-05-13
Last Modified: 2013-11-27
I  need to create a file for each month and then write data from an Access table to each month's spreadsheet.  I created one template, wrote a VBA module from the Access side to make copies of the template for each month and then use DoCmd.TransferSpreadsheet, acExport to write each month's data to each spreadsheet.  The problem I'm encountering is that I need to reference the data on that tab with formulas in other parts of the spreadsheet.  The DoCmd.TransferSpreadsheet command won't write over the tab I reference, it creates a new tab so my cells don't reference the tab with the data in it.

I use:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Monthly_Opt_Inputs", "Y:\Susan and Rob\Equities\Backtests\Equity Optimizer " & Ret_Date & ".xls"

But because it sees that tab "Monthly_Opt_Inputs" is already an existing tab, it creates a tab called "Monthly_Opt_Inputs1".  Is there a way to force it to write over the tab I've specified?

Thanks, Susan    
Question by:kobys
    LVL 11

    Accepted Solution

    Susan, I asked this very same question yesterday!

    You should be able to adapt the very lasdt set of code to help you.

    Note the file path was contained in a text box on the form that I called the event from, you can easily hard code it

    Author Closing Comment

    Thanks!  This worked!
    LVL 11

    Expert Comment

    happy to help

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
    A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
    In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    737 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

    17 Experts available now in Live!

    Get 1:1 Help Now