Transferred spreadsheets do no overwrite

[Wayne Cramer]

I have an Access application where a macro transfers two spreadsheets to a designated directory. This works fine if the directory is empty but if old files are in the directory they are not overwritten. Is there a way for the transferred files to overwrite the existing files in the directory?

 

[boblarson]

Use this:

If Dir("YourfilepathAndFileNameHere")="" Then  ' Checks to see if a file exists
    'Put your transfer code here
Else
    Kill("YourfilpathAndFileNameHere") ' This will delete the file that exists
    ' Then put your transfer code here
End If

 

[GaryPanic]

would it not be better to have the file with a date on it - then you keep copies of what you ahve done ???

 

[boblarson]

would it not be better to have the file with a date on it - then you keep copies of what you ahve done ???

That's what I usually do. But, sometimes you might not want other copies lying around. So, whichever way they want to do it...

 

[Wayne Cramer]

Transferred xls files do not overwrite

Bob, I'm a little confused. Here is my existing code that trnasfers fine but does not overwrite the files. Where do I place your code?

Function mcrSend_Efficiency_Reports_REVX()
On Error GoTo mcrSend_Efficiency_Reports_REVX_Err

DoCmd.SetWarnings False
Beep
MsgBox "Enter the beginning and ending dates for the efficiency report", vbInformation, ""
DoCmd.OpenQuery "qryMake_Spreadsheet_Table", acViewNormal, acEdit
DoCmd.OpenQuery "qryMake_Weekly_Efficiency_Report_Table", acViewNormal, acEdit
DoCmd.OpenQuery "qryException_Report", acViewNormal, acEdit
DoCmd.TransferSpreadsheet acExport, 8, "tblWeekly_Efficiency_Report_Spreadsheet", "H:\MANAGEMENT\Efficiency Reporting\Reports Out\Efficiency Report " & Format(Date, "mm-dd-yyyy") & ".xls", True, ""
DoCmd.TransferSpreadsheet acExport, 8, "tblEfficiency_Report_Exceptions", "H:\MANAGEMENT\Efficiency Reporting\Reports Out\Exception Report " & Format(Date, "mm-dd-yyyy") & ".xls", True, ""


mcrSend_Efficiency_Reports_REVX_Exit:
Exit Function

mcrSend_Efficiency_Reports_REVX_Err:
MsgBox Error$
Resume mcrSend_Efficiency_Reports_REVX_Exit

End Function

Wayne