Creating a backup that runs once a week

[dpaton05]

I have a spreadsheet that I want to create a backup copy of the file once a week upon opening, for instance, when the spreadsheet is opened, the date of the last backup is checked and if the date it was created is more than 1 week ago, it will create a backup file.

This spreadsheet has a folder called Backups in the same folder it is in, which is where the backups are to be stored. The filename of the backup files are to be structured like this. Firstly, it needs the word Backup, then the name of the file that is being backed up, without the extension then it needs the date and time that it was backed up. The format for the last bit needs to be dd.mm.yy MMHH AM/PM, for instance "Backup Filename 24.3.21 129PM"

I found a very similar post that I posted a bit under a year ago but it was asking for a daily backup. I want a weekly backup now and I was not quite sure how to modify it. A lot of the other details are the same but any details in this post, takes precedence over details in that post I found.

Backups

I have a spreadsheet that is going to record data that I don't want to lose. Therefore, I want to insert a feature that runs a daily backup and saves a copy of the entire workbook as a separate file. I need the file saved with the filename "Allocation sheets - dd.mm.yyyy". The dd.mm.yyyy...

www.mrexcel.com

I also want the ability to easily change the frequency of Backups. Possibly by having a cell that I can enter in weekly, fortnightly or monthly to set the frequency. This can be done on a sheet with the code name data, somewhere between A37:A50.

Could someone help me please?

 

[Michael M]

Maybe this will help make it a bit clearer

MANUALLY put todays date in "A1"(not a formula) and the =Today() formula in "A2" of that sheet BEHIND the logo....
Once that has been done, YOU SHOULD NEVER NEED TO DO IT AGAIN, as the code below will adjust the dates when the criteria is met....The criteria being, when the dates are 7 days or more different !!

Does this help ???

Sub BackupFile()
Dim sFileName As String, fname As String, fpath As String
' the variable for the workbooks current directory
fpath = ThisWorkbook.Path
'the variable to create the filename
fname = Format(Now, "dd-mm-yy hhmm AM/PM")
'using the Backups Sheet
With Sheets("Backups")
' this line checks to see if the current date is 7 days or more
' newer than the date stored in "A1"
    If Date - Range("A1").Value >= 7 Then
'If it's not 7 days different then do nothing
' if the date IS newer then it inputs a new date in "A1"
    .Range("A1").Value = Date
' it then converts the date to a value rather than a date
' so it can't change each day
    .Range("A1").Value = .Range("A1").Value
' this puts the date into "A2" which will change each day
    .Range("A2").Value = Date
' this saves the workbook to the designated filepath
    ActiveWorkbook.SaveCopyAs Filename:=fpath & "\Backups\" & fname & ".xlsm"
    End If
End With
End Sub

 

[dpaton05]

Thank you so much for that explanation Michael, that makes more sense when you explain what each line is doing.

 

[Michael M]

Glad it helped ?

 

[dpaton05]

Tried it at work today and it works brilliantly!! Thank you!!

 

[Michael M]

Woo Hoo, a win on a Monday.....can only go one way from here this week...

 

[dpaton05]

Actually, it has stopped working now. I try and open the file and I get an error, Type mismatch with the following line highlighted

If Date - Range("A1").Value >= 7 Then

I don't understand why it is not working now as I have not done anything to it, just had to leave for a meeting

All I did was call the above procedure from the open procedure.

Sub Workbook_Open()

Application.WindowState = xlMaximized
Call BackupFile
Dim file1 As Integer
Dim strLine As String
file1 = FreeFile
If Not ActiveWorkbook.ReadOnly = True Then
    'only add name to the usage log if the user has it locked
    Open ThisWorkbook.Path & "\usage.log" For Append As #file1
    Print #file1, Environ("USERNAME") & ". Please close any allocation sheets that has been opened" _
    & " WITHOUT SAVING THEM. Then contact the user that has it open or wait until they are finished."
    Close #file1
Else
    'if someone else has the file open, find out who
    Open ThisWorkbook.Path & "\usage.log" For Input Access Read As #file1
    Do While Not EOF(file1)
    Line Input #file1, strLine
    Loop
    Close #file1
    'Assign true to the UnsafeToDelete variable to identify someone has it open
    UnsafeToDelete = True
    MsgBox "The following user has the allocation sheets open: " & strLine
    'If UnsafeToDelete is true, a user has the allocation sheet open, so exit sub
    If UnsafeToDelete = True Then ThisWorkbook.Close False
End If
End Sub

 

[Michael M]

What is in

Range("A1").Value

 

[dpaton05]

Todays date, as I was able to run the backup earlier.

 

[dpaton05]

I think what happened was I was trying to get the filename inserted in front of the date so the format read "Backup xxxxxxx 29/3/21 1013 AM" and it gave me an error, so I reverted back to the code in post 36 and that's when the error started.

 

[Michael M]

Shouldn't Call BackupFile be at the end of the code not the beginning ??