VBA Express : Excel - AutoBackup Workbook (Template)

Excel

AutoBackup Workbook (Template)

Ease of Use

Easy

Version tested with

2000

Submitted by:

johnske

Description:

A backup copy of the active workbook is created and saved in 'My Documents' each time the active workbook is saved.

Discussion:

'Word' has the facility to create automatic backups as required, or, when the document is closed. This is particularly handy when several changes are made before it's decided that changes simply aren't working out as you planned. You can then open and revert to a previous version and start again from that version - Excel doesn't provide this facility. This rectifies that situation and allows you to revert to a previous version at any time.

Code:

instructions for use

			Option Explicit 
 
Private Sub Workbook_Open() 
    Application.Caption = "Microsoft Excel AutoBackup" 
End Sub 
 
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) 
    Dim MyFilePath$, Extension$ 
    MyFilePath = MyPCpath("MyDocuments") 
    Extension = Left(ThisWorkbook.Name, Len _ 
    (ThisWorkbook.Name) - 4) & " Backup" 
     
    On Error Resume Next '<< folder exists
    MkDir MyFilePath & Extension '<< create folder
     'save current version of this book in the folder
    ActiveWorkbook.SaveCopyAs Filename:=MyFilePath & _ 
    Extension & "\" & Extension & _ 
    (Format(Now, " mmm d yyyy, hh.mm.ss AMPM")) & ".xls" 
End Sub 
 
Public Function MyPCpath$(Folder) 
    MyPCpath = CreateObject("WScript.Shell").SpecialFolders _ 
    (Folder) & Application.PathSeparator 
End Function

How to use:

Open an Excel workbook
Select Tools/Macro/Visual Basic Editor
In the VBE window, select Tools/Project Explorer
Select the ThisWorkbook module
Copy and paste the code above into the Module
Now select File/Close and Return To Microsoft Excel
Select File/SaveAs... and enter the name 'AutoBackup Workbook' then Save.

Test the code:

Either follow the instructions above or download the attached zip file...
Now select File/SaveAs... In the browser window that appears, select the 'Save as type:' pane and then select 'Template' and Save.
Whenever you choose 'New Office Document' you will now have the option of choosing either a normal workbook (Blank Workbook) or an Autosave Workbook.
All your version backups will be found in 'My Documents' inside the folder with the active workbooks name followed by the suffix "Backup". (All the versions inside the folder are date and time stamped).
NOTE: If making many changes to workbooks, you may have very many versions of the same thing, so it would pay to open 'My Documents' and periodically cull the older versions.

Sample File:

AutoBackup Workbook.zip 11.04KB

Approved by mdmackillop

This entry has been viewed 301 times.