SaveAs inside BeforeSave (Excel, VBA)

Question

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!

I'm not sure if `Date` contains special characters that can not be contained in a file name. — , Nov 22 '17 at 13:30
are you missing a little comma `,` before the `FileFormat:=52` ? — Shai Rado, Nov 22 '17 at 13:31
@ David - Date does not cause problem. Excel crashes even without it. — Oleksandr Titorchuk, Nov 22 '17 at 13:40
Are opening a workbook on disk or creating new excel file from start menu or , opening it from email or other source besides your disk ? If the file is not already on disk Activeworkbook.path will not return anything. — PankajKushwaha, Nov 22 '17 at 13:45

newacc2240 · Accepted Answer · 2017-11-22T14:39:08.527

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.

Thank you for help! I think I will use your solution) – Oleksandr Titorchuk Nov 22 '17 at 17:50 — Oleksandr Titorchuk, Nov 22 '17 at 17:50

SaveAs inside BeforeSave (Excel, VBA)

1 Answers1