Macro to backup Excel file before saving

Macro to backup Excel file before saving

Place the following code in the ThisWorkbook module of an Macro enabled Excel file (Alt-F11)

https://stackoverflow.com/questions/15267796/macro-to-make-a-backup-while-saving-a-file

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

    Application.EnableEvents = False

    thisPath = ThisWorkbook.Path

    myName = Left(ThisWorkbook.Name, (InStrRev(ThisWorkbook.Name, ".") – 1))

    ext = Right(ThisWorkbook.Name, Len(ThisWorkbook.Name) – InStrRev(ThisWorkbook.Name, "."))

    'backupdirectory = myName & " backups"

    backupdirectory = "backup"

    Set FSO = CreateObject("Scripting.FileSystemObject")

    If Not FSO.FolderExists(ThisWorkbook.Path & "/" & backupdirectory) Then

        FSO.CreateFolder (ThisWorkbook.Path & "/" & backupdirectory)

    End If

    T = Format(Now, "mmm dd yyyy hh mm ss")

    ThisWorkbook.SaveCopyAs thisPath & "\" & backupdirectory & "\" & myName & " " & T & "." & ext

    Application.EnableEvents = True

End Sub

Tags:

No comments yet.

Leave a Reply