Excel - always save backup

Asked By Ron Patterson on 13-May-08 01:51 PM
Can someone please run me through the drill to configure Excel 2002 to
always save a backup file when the original is saved.
Ron Patterson

T. Valko replied on 13-May-08 02:04 PM
Open the file
Goto File>Save As
On the very right hand side of the dialog box select Tools>General
Options>Always create backup>OK
Click the Save button
Answer yes to the prompt

Microsoft Excel MVP
Ron Patterson replied on 13-May-08 02:43 PM
Thanks Biff -  but my fault.
I think I asked the wrong question.  I want EVERY file I create and save in
Excel to AUTOMATICALLY create a backup file as a default.  There is some way
to do this I recall copying a model file to the XL Start folder -  but I do
not remember the details.
Gord Dibben replied on 13-May-08 03:29 PM
To create a workbook and a worksheet template

Open a new workbook.  Customize as you wish, including the "Always create a
backup" per Biff's direcdtion.

File>Save As Type:  scroll down to Excel Template(*.XLT) and select.  Name your
workbook "BOOK"(no quotes). Excel will add the .XLT to save as BOOK.XLT.

Store this workbook in the XLSTART folder usually located at........

C:\Documents and Settings\username\Application Data\Microsoft\Excel\XLSTART

This will be the default workbook for File>New or the Toolbar button File>New or
CTRL + n

WARNING................Do not use File>New...Blank Workbook or you will get the
Excel default workbook.

NOTE:  Existing workbooks are not affected by these settings.

You can also open a new workbook and delete all but one sheet.  Customize as
you wish then save this as SHEET.XLT in XLSTART folder also.  It now becomes
the default Insert>Sheet.

More can be found on this in Help under "templates"(no quotes).

Gord Dibben  MS Excel MVP

On Tue, 13 May 2008 11:43:13 -0700, "Ron Patterson" <a.baba@mindspring.com>
Ron Patterson replied on 13-May-08 04:44 PM
Thanks Gord  -  this was what I was trying to remember.
The only problem seems to be that if I create a new workbook and save it,
there is not a backup created.  But if I save it and then click save again
there is a backup created.  Any way to avoid having to remember to save it
twice to create a backup.
T. Valko replied on 13-May-08 05:21 PM
The backup file (*.xlk) is always one version behind the current file

When you open a brand new workbook it doesn't exist until you actually save
it. So, until you actually save the file for the first time and it already
exists there is nothing to backup.

I understand what you want to do but I'm not sure how to go about doing it.
It will require some kind of VBA code but it will have to be written such
that you just don't end up with an exact copy of the current file. That's
not the purpose of having  a backup.

Gord's much better with VBA than I am. Maybe he has an idea.

If you go about things the way they are, the next time you save the current
file then you'll get the backup which will be one version behind the current

Microsoft Excel MVP
Ron Patterson replied on 13-May-08 06:25 PM
Understood now -  sure makes sense the way you explained it.
Thanks Biff.
Gord Dibben replied on 13-May-08 06:32 PM
Biff has hit the nail on the head about having to save twice in order to have a
backup one version earlier than current.

This code will ensure that you save twice with the backup created when you click

The code will not run on subsequent openings of the created file because it will
then have a path.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Application.DisplayAlerts = False
If ThisWorkbook.Path = "" Then
ThisWorkbook.SaveAs Filename:=ActiveWorkbook.Name
End If
Application.DisplayAlerts = True
End Sub

Of course it means you will get the "enable macros" warning.

Place the code in Thisworkbook module of the Book.xlt

Hopefully someone can come up with something better.


On Tue, 13 May 2008 13:44:10 -0700, "Ron Patterson" <a.baba@mindspring.com>
Ron Patterson replied on 13-May-08 09:17 PM
Well Gord, I am sure your code will work.  I just have no idea how or where
to write it  -  or better  -  where to put it.  All this stuff that is
second nature to you, is beyond my nascent computing skills.

However, testing has just shown me that if I create a new workbook and Save
it and then click on Save again before closing - I get an exact copy.  And
every time I edit that file - if I can remember to click Save twice - it
will save the current copy.

This is probably easier for a stunted intellect.

Thanks to both of you for your kind assistance.

Gord Dibben replied on 13-May-08 10:50 PM

The code I posted will be placed in Thisworkbook module of the original Book.xlt

Open that original by right-click on it and "Open".  That's the way you get the
original, not a copy, opened for edting.

With Book.xlt open right-click on the Excel icon left of "File" on menu.

Select View Code and copy/paste the code into that module.

Alt + q to return to Excel window.

Save the Book.xlt and close it out.

Now hit File>New and Book1 will open with a macro warning.

Enable macros.

Make a few changes to Book1 then save or save as.

One version will be saved immediately and you will be asked if you want to
overwrite that version.

Click yes and it re-saves with a backup named "backup of filename.xlk"


On Tue, 13 May 2008 18:17:13 -0700, "Ron Patterson" <a.baba@mindspring.com>