Excel Autosave Backup Macro

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:

Download two files.  First is the full AutoSave Installation Instructions, and second is the Macro itself.  Difficulty level of installation is Easy so no worries there.

Code Itself:

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.

Sub AutoSaveBackup()
‘**Author: John@EconEngineer
‘*Website: https://econengineer.wordpress.com/category/productivity/excel-addins/
‘****Date: 01/07/2012
‘*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)
End If

‘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/&#8221;)
Exit Sub
End If

‘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/&#8221;)
ElseIf Dir(SavePathDir & BackupName) = vbNullString Then
ThisWorkbook.SaveCopyAs (SavePathDir & BackupName)
End If
End If

End Sub
Private Sub Workbook_Open()
‘**Author: John@EconEngineer
‘*Website: https://econengineer.wordpress.com/category/productivity/excel-addins/
‘****Date: 1/7/2012
‘*Purpose: Upon Opening this calls the Autobackup sub.

Call AutoSaveBackup
End 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.

Advertisements

2 thoughts on “Excel Autosave Backup Macro

  1. Hi
    This is exactly what I require for my workbook, but I cannot get the file to archive. It keeps telling me the archive folder is not present… I’ve followed the details exactly and I cannot get it to find it.

    I’ve even started a new one from scratch and nothing – I’m using 2007 edition

    Please help

    • Hi Lee, is the backup path a subfolder of the original workbook? The SavePathDir string essentially takes the workbook directory and adds the backup path inputted in the “User Editable Directory” . => SavePathDir = ThisWorkbook.Path & BackupPath

      If you want the backup path to be a completely separate path from the workbook, you should be able to put the full C:\… directory in the BackupPath string and modify this line:
      SavePathDir = ThisWorkbook.Path & BackupPath
      to read:
      SavePathDir = BackupPath

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s