DoCmd.TransferSpreadsheet export

Posted on 2011-05-13
Medium Priority
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
  • 2
LVL 11

Accepted Solution

Runrigger earned 2000 total points
ID: 35754644
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

ID: 35754948
Thanks!  This worked!
LVL 11

Expert Comment

ID: 35754997
happy to help

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

839 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