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]

Like I said in an earlier post.....I belive the 2nd "s" in Backups was an issue

"\Backups\Backup"

 

[dpaton05]

The error occurred again. Not sure if I have described this error or not. I thought I had but I just looked through the thread and I couldn't find it.

When I try and open it the reason it occurred is different to that described earlier. If the backups sheet is hidden or very hidden I get the error but if the sheet is visible the error does not occur. This tells me that the error is not related as I have noticed that vba throws all different kinds of errors sometimes for certain errors.

The error is Type Mismatch with the following line highlighted

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

 

[dpaton05]

I tried to write code to make the sheet visible before the backup is run and hide it afterwards but I got the error Object doesn't support this property or method with this line highlighted.

.vlsible = xlSheetVisible

My whole sub....

Sub BackupFile()
Dim sFileName As String, fname As String, dt As String, fpath As String
' the variable for the workbooks current directory
Call TurnOnFunctionality
fpath = ThisWorkbook.Path
'this creates the variable dt that is made up of the date, time and whether it was AM or PM
dt = Format(Now, "dd-mm-yy hhmm AM/PM")
'the InStr function returns how many characters the .x is from the left in the filename.
'The -1 moves the InStr number 1 position to the left from the .x, which is the start of the extension.
'The left function returns the number of characters from the left that InStr returned, which is
'the character 1 position before the extension which returns the filename without the extension.
fname = Left(ActiveWorkbook.Name, InStr(ActiveWorkbook.Name, ".x") - 1)
'using the Backups Sheet
    With Sheets("Backups")
        .vlsible = xlSheetVisible
        ' 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 with the word backup,
            'the original filename and the date saved in the new filename
            ActiveWorkbook.SaveCopyAs FileName:=fpath & "\Backups\Backup" & " " & fname & " " & dt & ".xlsm"
        End If
        .Visible = xlSheetVeryHidden
    End With
End Sub

 

[dpaton05]

I tried to put the .visible code in the workbook open procedure and I get the error message Subscript out of range with the End Sub highlighted in the workbook_open procedure

Sub Workbook_Open()

Application.WindowState = xlMaximized
Dim file1 As Integer
ThisWorkbook.Sheets("Backups").Visible = True
Call BackupFile
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
ThisWorkbook.Sheets("Backups").Visible = xlVeryHidden

End Sub

 

[dpaton05]

Some further info

If I have the sheet Backups visible property set to true when I open it, the date in A1 is changed to the current date and I get the error Subscript out of range with the last line highlighted in the workbook_open procedure

End Sub

in

Sub Workbook_Open()

Application.WindowState = xlMaximized
Dim file1 As Integer
ThisWorkbook.Sheets("Backups").Visible = True
Call BackupFile
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
ThisWorkbook.Sheets("Backups").Visible = xlVeryHidden

End Sub

But if the visible property is set to very hidden on the backups sheet when I try and open it, I get the error described in post 72 and the date remains the same in A1.

This is really confusing me

 

[dpaton05]

YAY!!! I worked it out. This line of code needed a dot before the word range. I knew it was some completely unrelated error.

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

 

[Michael M]

With all due respect Dave, You've kinda brought this upon yourself.
I suggested that you use one of the sheets already in use.... The startup Sheet for instance.
Put the same data in cells A1 & A2 of an existing sheet, make the font white, and leave it at that. then let the workbook_open event run on that sheet.
Hidden sheets, locked sheets all will cause issue for your workbook !!!

 

[dpaton05]

But I got it to work. The extra full stop fixed it.