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).

BackupButton

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
Else
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

______________________________

You may also like...

8 Responses

  1. sam says:

    I use AutoSafe from JKP…. its great

  2. Thanks Sam. I use it too :-)

  3. Doug Glancy says:

    I wrote a macro that saves a copy to the same folder structure on another drive (it creates the folders if not already there) and then does a normal save. That way I always have a recent backup to go to until the next time I use the macro. Aside from that, I just drag lots of copies in Windows Explorer (literally “Copy (2) of …xls) until I reach some kind of “no turning back” spot. Then there’s also the zip files and emails of previous versions.

    Love your blog!

  4. Keep in mind that using a macro to make a backup wipes out the undo information. This can be a problem with code that backs up the file automatically.

    Consider this: You accidentally delete a large range of data. The backup macro kicks in, and destroys the undo stack so you can’t undo your mistake.

  5. Thanks for the reminder, Sam and Jan Karel — I’ve added a link to AutoSafe in the article.

    Doug, thanks for describing how you handle backups, and I’m glad you like the blog!

    John, that’s a good point. My backup macro only runs when I click the button, so that gives me control over when to save. And since it saves to a different file, I could always close the current file without saving, to restore to the previously saved copy.

    Thanks Nicolai — I hope some of these ideas helped you.

  6. Frank says:

    This sounds like a great feature, but can you give a step by step for people who are not fluent in excel language, ie. go to Macro’s, go to view,then type a name create, etc.

  1. December 15, 2008

    […] Back up excel files while you work using VBA […]

Leave a Reply to John Walkenbach Cancel reply

Your email address will not be published. Required fields are marked *