Have you ever realized that you’ve made a mistake and are unable to go back in time? Or had something crash and it became unrecoverable? Sure you have, we all have. That’s why backups and autosaving is so important. Microsoft products are pretty nice in that they autosave your work fairly often so that in the event of a crash it is most likely recoverable. However, in the event of a mistake, or wanting to go back and review an earlier version of the document can’t be done unless you habitually make copies. Frankly, few of us have the time and motivation to do that. That’s where this short, easy to use macro comes in handy.
The AutoSaveBackup macro will:
- Make a backup anytime the file is opened (or at most once a day).
- Store all backups in a separate, specified folder (so your main spreadsheet folder isn’t cluttered with backups)
- Appends the Date upon backup creation to the Backup for easy sorting and future reference.
- Can create an up-to-date read-only Copy version of the spreadsheet for others to look at if they don’t need to modify the spreadsheet
- Great for spreadsheets used as databases where one person inputs data, and others will refer back to it.
- Contact me for the read-only Copy version of the code
Instructions for Macro Installation:
Everything you need is in the above mentioned files, but below is the code itself. I do recommend downloading the code rather than copying/pasting from here as putting the code into a blog format instead of coding format may distort the code and cause minor errors.
‘*Purpose: Autosaves a backup of this Workbook whenever called
‘ Typically is called every time it this workbook is opened, and will save at most one copy a day.
‘ Tested in Excel 2003 & 2010
‘Declares variables to be used
Dim SavePath As String
Dim BackupPath As String
Dim BackupName As String
Dim OriginalName As String
Dim SavePathDir As String
Dim FileExt As String
‘************** START OF USER EDITABLE INFORMATION ***************
‘Location where Backups will be automatically saved.
‘The first and last key between the quotation marks need to be a backwards slash “\” as below.
BackupPath = “\Archive\PersonalAddresses\”
‘What the backups will be called, key in whatever name you want between the quotation marks.
BackupName = “PersonalAddresses_Backup”
‘File name of the original file
‘If the file you wish to auto-backup is called PersonalAddresses.xls then
‘the text below should read “PersonalAddresses” (no .xls or .xlsm)
OriginalName = “PersonalAddresses”
‘*************** END OF USER EDITABLE INFORMATION ****************
‘Adds the excel file extension to the backup.
If Left(Right(ThisWorkbook.Name, 4), 1) = “.” Then
FileExt = Right(ThisWorkbook.Name, 4)
ElseIf Left(Right(ThisWorkbook.Name, 5), 1) = “.” Then
FileExt = Right(ThisWorkbook.Name, 5)
‘Sets up the Path where backups will be saved.
SavePathDir = ThisWorkbook.Path & BackupPath
‘Sets up the name for the Backup as what the User requested, and appends the date backuped
BackupName = BackupName & “_” & Format(Now(), “yyyy” & “-” & “mm” & “-” & “dd”) & FileExt
If ActiveWorkbook.Name = ThisWorkbook.Name Then
‘Checks that this macro isn’t run on a Backup File itself (No backups up backups)
If ThisWorkbook.Name <> OriginalName & FileExt Then
MsgBox (“Backup NOT created, this Excel Workbook may not be the Original Version.” & Chr(13) & “Contact Spreadsheet Administrator for Assistance” & Chr(13) & “https://econengineer.wordpress.com/”)
‘Checks that Backup Directory exists, then checks that prior backup hasn’t been already made for today and then makes the backup
If Dir(SavePathDir, vbDirectory) = vbNullString Then
MsgBox (“Backup NOT created. Backup/Archive folder does not exist.” & Chr(13) & “Contact Spreadsheet Administrator” & Chr(13) & “https://econengineer.wordpress.com/”)
ElseIf Dir(SavePathDir & BackupName) = vbNullString Then
ThisWorkbook.SaveCopyAs (SavePathDir & BackupName)
Private Sub Workbook_Open()
‘*Purpose: Upon Opening this calls the Autobackup sub.
The Macro itself is “protected” by the following:
Non-Legal, Non-Binding Notice:
This document is made in freedom and can be freely distributed. Reference of the author’s authorship is appreciated but no legal action will ever be made for copying or using this document as you see fit. While legal action may not be made, the author reserves rights to not like you very much for doing anything of detestable nature and to restrict your access to the author’s work if deemed appropriate.