Creating a backup that runs once a week

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
2,186
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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.


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

Well-known Member
Joined
Oct 27, 2005
Messages
20,151
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Like I said in an earlier post.....I belive the 2nd "s" in Backups was an issue
VBA Code:
"\Backups\Backup"
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
2,186
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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
VBA Code:
If Date - Range("A1").Value >= 7 Then
 

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
2,186
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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.
VBA Code:
.vlsible = xlSheetVisible

My whole sub....

VBA Code:
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

Well-known Member
Joined
Aug 14, 2018
Messages
2,186
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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

VBA Code:
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

Well-known Member
Joined
Aug 14, 2018
Messages
2,186
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

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
VBA Code:
End Sub

in

VBA Code:
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 :unsure:
 

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
2,186
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
YAY!!! I worked it out. This line of code needed a dot before the word range. I knew it was some completely unrelated error.

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

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,151
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
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

Well-known Member
Joined
Aug 14, 2018
Messages
2,186
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
But I got it to work. The extra full stop fixed it.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,713
Messages
5,637,932
Members
416,993
Latest member
ant8989

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top