Posted on 2011-05-13
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.
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?