Automatic Backup and Time Stamp Workbooks

Using Task Scheduler and Macros, you can easily run daily or weekly backups of important workbooks with a timestamp. 

 

Steps

1. Create Auto_Open workbook

2. Schedule Task

3. Done

 

 

Watch a 3 minute tutorial video, or read on and download below!

1. The Objective

I wanted to backup an important file every week, and have a time stamp in the name for easy sorting and to ensure it didn't overwrite previous backups. This is very easy to do once you have an understanding of Auto_Open workbooks and Task Scheduler. 

I recommend you read my Task Scheduler tutorial first. 

 

2. Building The Sheet

1. Create workbooks
Create a "Backup" folder on your desktop. 
Within that, create 3 blank items - "Important Workbook.xlsx", "BackupScheduler.xlsm" and a folder "Backups Of Important Workbook".

Task scheduler is going to launch "BackupScheduler.xlsm", which contains our macro. This macro will load up "Important Workbook.xlsx", update the links and save it with a timestamp in the empty folder. 

Now is the time to familiarize yourself with Auto_Open macros and Task Scheduler with my Macro Schedule Tutorial. 

2. Add in VBA code to "BackupScheduler.xlsm"
Alt+F11 to bring up the VBA editor. Right click on "ThisWorkbook", click "Insert", click "Module".
Double click on "Module 1". Copy in the code below:

Sub AutoOpen() '**************Change this to Auto_Open******************
Application.DisplayAlerts = False
Application.ScreenUpdating = False

'Change this with your file location!
Workbooks.Open Filename:="C:\Users\username\Desktop\Backup\Important Workbook.xlsx" _
, UpdateLinks:=3

'Change this with your file location!
Workbooks("Important Workbook.xlsx").SaveAs Filename:="C:\Users\username\Desktop\Backup\Backups Of Important Workbook\Important Workbook " & "(Backup) " & Format(Now(), "(yyyy-mm-dd hhmm)") & ".xlsx"

ActiveWindow.Close

Workbooks("BackupScheduler.xlsm").Close SaveChanges:=True

Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

Ensure the code has been updated with your locations and file names before continuing.

3. Test Auto_Open
Save and exit the workbook. Click on BackupScheduler.xlsm, it should run and then close (it might want you to enable macros first, which you can disable in the trust center settings). 

Look in your "Backups Of Important Workbook" folder, you should have a time stamped sheet.

timestamped file.png

Run it again you it will add another time stamped file. 

If you have problems, recheck your VBA code and make sure the locations are all correct. To edit the Auto_Open file, open the workbook in protected view.

4. Create the scheduled task
Follow the same process outlined in my Task Scheduler Tutorial. 
Set it to recur at your desired interval, and make sure "Add arguments (optional):" is set to the location of BackupScheduler.xlsm

3. Summary

That's it, you now have an auto backup feature using Excel! 

I use it a lot for important sheets like Budgets and Accounts to make sure they are never tampered with or broken. Worse case scenario we just go back one week. 
To make everything more secure, download Google Drive desktop, and save the backups to a folder in there to enable cloud backups!

If you want to do this without using Excel, follow my blog on how to use a cool Windows Script Xcopy.

4. Download

More templates