ByPassing Do You Want To Overwrite The File Message Box

[Simba1]

Hi guys,

I've created a macro that saves a copy of my file to a specific location once the workbook is closed. I've encountered a problem if the user saves the file twice in the same day - it asks if I want to replace the previous version - I just want it to save or overwrite the file.

Can anyone look at my code and advise me on how I can bypass the Msgbox that pops up saying "A File named xxx.xls already exists in this location. Do you want to replace it?" I get an error if I press Cancel or No. What I want to do is have it overwrite the file without any prompt or confirmation.

Thanks

*The Call DeleteMenu is not needed here...its for another function I made.

Sub Workbook_BeforeClose(Cancel As Boolean)
    Call DeleteMenu
    ActiveWorkbook.SaveAs Filename:="G:\PUBLIC\Demand Planning\Backup of DP Reports\PRODUCT MASTER" & Format(Now(), "ddmmyy") & ".xls"
    Application.DisplayAlerts = False
    ActiveWorkbook.Close
End Sub

 

[Richie(UK)]

Hi,

Add the line:

Application.DisplayAlerts=False

before the save instruction and then switch back to True again when done.

HTH

 

[Simba1]

Thanks Richie!

I guess you can tell I'm a beginner at VBA.