Transfer Spreadsheet overwrite

[bcallnan]

Hello All-

I have a large excel workbook that runs off one data sheet. Each worksheet refers back to this sheet to build graphs, pivot tables etc. In the past I have used transfer spreadsheet and it has simply overwrittem the worksheet:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "dbo.tbl_Temp_PBS_Gen_Daily_Final", strfilename, True

for some reason it does not seem to overwrite in this new application I am building. Here is all of the code.

            If response = vbOK Then
                Objexcel.DisplayAlerts = False
                DoCmd.SetWarnings False
 
                Application.Echo True, "Saving Information As An Excel File"
                Workbooks.Open sourcefilename, , False
                ActiveWorkbook.SaveAs (strfilename)
 
                ActiveWorkbook.Close
                Workbooks.Close
 
                Set Objexcel = Nothing
                Call Calculate_Gen_Runtime(tbo_Begin_Date_GR, tbo_End_date_GR)
                DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "dbo.tbl_Temp_PBS_Gen_Daily_Final", strfilename, True
 
                message = "The File has been exported"
                response = MsgBox(message, vbOKOnly, title)
 
                DoCmd.SetWarnings True
 
 end if

If anyone has an answer to this it would be fantastic! I currently work around it by opening the workbook, deleting the data worksheet, saving the workbook, closing the workbook, and then exporting the table to the worksheet. The problem with this is that I have the "refresh all data" option checked in the workbook, so this process takes a good deal of time. Any way to "Force" overwriting of the worksheet?
I have researched this and the most common answer seems to be to delete the file. I am doing that and it does work, but overwriting the worksheet would save me all knids of time! The opening and closing of the workbook eats up alot of time.
Thanks!!
-Brian