A complete backup macro

[Vallenato]

Hello!
I am looking for a excel macro that automatically does a backup each week (if I am not using the file for more than a week it will automatically do a backup as soon as I start the file).

The backup should be named the file name followed with the backup date and be placed in another folder.

If there is more than 10 old backup files in the folder already the macro should delete the oldest.

(If possible (but I guess not) the macro should also automatically once a month send an automatic e-mail to a certain e-mailadress containing a backup of the file. But I guess maybe this is not possible so exclude this wish from the list if it doesn´t work...)

Does anyone have a vba code for this?

 

[mse330]

Place the below code on "ThisWorkbook" in your VBA editor & it will ask you if you need to take a back-up upon opening the file every Monday (first day of the week) which you may change as desired

Sub workbook_open()

Dim FileName As String
Dim FileExt As String

FileName = Left(ActiveWorkbook.Name, InStr(ActiveWorkbook.Name, ".") - 1)
FileExt = Right(ActiveWorkbook.Name, (Len(ActiveWorkbook.Name) + 1 - InStr(ActiveWorkbook.Name, ".")))

If Weekday(Date, vbMonday) = 1 Then  ' vbMonday means your week starts on Monday which is weekday #1 
    If MsgBox("Would you take a back up of the file?", vbInformation + vbYesNo) = vbYes Then
        ActiveWorkbook.SaveCopyAs "C:\ ... your path goes here ... " & FileName & " - " & Format(Date, "dd.mmm.yyyy") & FileExt
    End If
End If

End Sub

 

[Vallenato]

Hello!
Thanks for the code. Can you also make it function so if there is more than 10 old backup files in the folder already the macro should delete the oldest? Thanks