Creating a backup that runs once a week

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
2,209
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,168
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Won't make any difference if the fpath or fname are wrong
But try
VBA Code:
Sub BackupFile()
Dim sFileName As String, fname As String, dt As String, fpath As String
' the variable for the workbooks current directory
fpath = ThisWorkbook.Path
'the variable to create the filename
dt = Format(Now, "dd-mm-yy hhmm AM/PM")
' return the filename WITHOUT the file extension
fname = "Backup " & Left(ActiveWorkbook.Name, InStr(ActiveWorkbook.Name, ".x") - 1)
'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 & " " & dt & ".xlsm"
    End If
End With
End Sub
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
2,209
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
This is so strange. I moved the file from my network drive where I was developing it to the network drive at my work, tried the same changes and it worked fine. :unsure:
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,168
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
See post #61, with changes
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,168
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows

ADVERTISEMENT

my response from post #60
VBA Code:
fname = "Backup " & Left(ActiveWorkbook.Name, InStr(ActiveWorkbook.Name, ".x") - 1)
 

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
2,209
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Oh yeah, I see what you did now. Thanks Michael.
 

dpaton05

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

ADVERTISEMENT

NNNOOOOOOOOOO!!!!!!!!!

Its stopped working again. Now it says Type mismatch and highlights this line
VBA Code:
If Date - Range("A1").Value >= 7 Then

Sorry to be so annoying Michael.
 

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
2,209
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Does A1/A2 need to be any specific format as they are formatted As date?
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,168
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
both should be formatted as Date !
 

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
2,209
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
This is crazy Michael, I got in to work today, tried it again and it worked fine using this line of code

VBA Code:
ActiveWorkbook.SaveCopyAs FileName:=fpath & "\Backups\Backup" & " " & fname & " " & dt & ".xlsm"

Thanks for your help.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,984
Messages
5,639,391
Members
417,085
Latest member
Aman2626

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