Creating a backup that runs once a week

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
2,352
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?
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
Maybe this one, that does it every Monday
 
Upvote 0
Thanks Michael but how would I make it without the extension and in this format "Backup Filename 24.3.21 129PM", where the end is the date and time the backup is run?
 
Upvote 0
You will have to provide the File path and directory to be saved to for fPath
VBA Code:
Private Sub Workbook_Open()
  ' ~~ Run on Mondays || [URL="http://www.ozgrid.com/forum/showthread.php?t=27427#4"]if today is Monday(weekday)[/URL]
  If Weekday(Now) = (vbMonday) Or Day(Now) = 1 Then
    With ActiveWorkbook
    .SaveAs Filename:=fpath & "Backup File " & Now() & ".xlsm", FileFormat:=52
      .Save
    End With
  End If
End Sub
 
Upvote 0
Thanks Michael. If the file is not opened on the Monday , I want the backup to run on the next day following the Monday. Does this code do that?
 
Upvote 0
Nope....it backs up every Monday or whatever day you code it to do every week !
 
Upvote 0
Thanks for that code Michael. How could I modify it so if the file is not opened on a Monday, hence, the backup is not run, the backup will be run on the next day that it is opened?
 
Upvote 0
Why not simply run a bacup when it is opened, regardless of the day !!
 
Upvote 0
I guess that is a good solution. Is it possible to create code to

  1. check the date of creation of the most recent backup, each time the file is opened.
  2. If it is more than 1 week since that point, make a copy of the file as a backup and store it in the backup directory with the specified naming convention.
 
Upvote 0
Would you be able to help me with code for this please Michael?
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,317
Members
448,564
Latest member
ED38

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
Back
Top