will introduce you to the exciting new pivot table features in Excel 2007. Create quick summaries and pivot charts, add impact with traffic light icons, design calculated fields, group dates and numbers.
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
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
______________________________
December 15th, 2008 at 12:26 am
I use AutoSafe from JKP…. its great
December 15th, 2008 at 3:26 am
Thanks Sam. I use it too
December 15th, 2008 at 10:52 am
[...] Back up excel files while you work using VBA [...]
December 15th, 2008 at 11:22 am
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!
December 15th, 2008 at 12:12 pm
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.
December 15th, 2008 at 6:07 pm
nice tip!
December 15th, 2008 at 6:38 pm
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.
January 15th, 2009 at 12:24 pm
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.