Search code examples
excelvbabefore-save

SaveAs inside BeforeSave (Excel, VBA)

I want to program Excel to create back-up of my file before saving it.

But each time I am trying to use it, Excel crashes.

Can please some explain me why this happens even if I insert Application.EnableEvents = False to prevent infinite loop?

The code I am using is below:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Application.EnableEvents = False
    ActiveWorkbook.SaveAs Filename:=ActiveWorkbook.Path & "\" & "BackUp_" & Date & "_" & ActiveWorkbook.Name, FileFormat:=52
    Application.EnableEvents = True
End Sub

Thank you in advance for help!

Solution

  • Edit: I found the reason. SaveAs will close the original workbook without saving, and open the new one automatically. Therefore excel can't execute the origin saving script.

    Instead, using SaveCopyAs could build a copy in background, thus the origin file is still alive in your window.

    Try this:

    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    ActiveWorkbook.SaveCopyAs Filename:=ActiveWorkbook.Path & "\" & "BackUp_" & Format(Date, "YYYYMMDD") & "_" & ActiveWorkbook.Name
End Sub

    Note: Calling Date directly could be like 2017/11/22 which contains / ,which is an invalid character, using a Format function can prevent an error.