Excel Backups While You Work

How do you save your file while working in Excel?

  • Do you click the Save button, and save over the previous version?
  • Do you use Excel’s AutoSave feature?
  • Or the AutoSafe utility by Jan Karel Pieterse?
  • Do you choose Save As, and save the file with a different name?
  • Something else?

I like to have different versions of a file, so I can go back to a previous version if something goes horribly wrong. So, I created a macro to save my files, and added a button to the toolbar (or QAT in Excel 2007).
The macro saves the file in a specified folder, adding the year, month, day, hour and minute to the file name. For example, if the file I’m working on is named Budget2009.xls, the backup file would be named Budget2009_20081215_1008.xls if I saved it at 10:08 AM today.

The Macro Code

I store this code in my Personal.xls file, because that workbook is always open when I’m using Excel. In the code, you can change the Save directory to one that you prefer on your computer or network. I use C:\Backups\
Sub SaveBUCopy()
Dim strFile As String
Dim strName As String
Dim lExt As Long
Dim strDir As String
Dim strExt As String

strName = ActiveWorkbook.Name
strDir = “C:\Backups\”

If UCase(Right(strName, 4)) = “.XLS” Then
lExt = 4
lExt = 5
End If

strFile = Left(strName, Len(strName) – lExt)
strExt = Right(strName, lExt)

ActiveWorkbook.SaveCopyAs strDir & strFile _
& Format(Now, “_yyyymmdd_HhMm”) & strExt
End Sub