Creating a backup that runs once a week

dpaton05

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

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,120
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Maybe this one, that does it every Monday
 

dpaton05

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

Michael M

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

dpaton05

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

ADVERTISEMENT

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?
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,120
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Nope....it backs up every Monday or whatever day you code it to do every week !
 

dpaton05

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

ADVERTISEMENT

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?
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,120
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Why not simply run a bacup when it is opened, regardless of the day !!
 

dpaton05

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

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
2,173
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Would you be able to help me with code for this please Michael?
 

Watch MrExcel Video

Forum statistics

Threads
1,129,500
Messages
5,636,690
Members
416,935
Latest member
Atulcp

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