save as .bak when opening .xls

[avi]

Is it possible to let Excel save the .xls file to .bak when opening it?
Preferably with a macro.
(I am using Excel 2003).

Cheers,
Avi

 

[Shane Devenshire]

Hi,

You could add a save as command to the Open_Workbook event. If you only
want this to happen when you open the workbook you will probably need to run
two consecutive saves - one to do the bak and another to resave the file to
where you opened it from. You should be able to record the steps and then
add them to the following:

Private Sub Workbook_Open()
'your code
End Sub

This code goes into the thisWorkbook object in the VBE. Press Alt+F11,
double-click the thisWorkbook object, for your workbook, in the Project
explorer near the top left of the screen.

 

[CLR]

If you really want an archived record of the workbook, then you should
consider just saving the file to an archive directory with a datecode
appended to the filename, and like Shane says, resaving to your regular
directory without the datecode........if you only save it as a .bak, it will
be overwritten each time you open the file anew....even if it has errors in
it.

Vaya con Dios,
Chuck, CABGx3

 

[avi]

Thank you. That worked. I just have two followup questions:

1. Is this possible to implement this for all .xls files (i.e. use it in
persnlk.xls) and, if so, what phrasing should I use in the macro to avoid an
actual filename in the macro - it should use something like current_workbook
(??).
2. Is it also possible to automatically answer the questions about
overwriting existing files with 'yes'?

Thank you again,
Avi.

 

[Dave Peterson]

You can use an application level event.

This goes in the ThisWorkbook module of your persnlk.xls workbook.

(persnlk.xls is the same as the USA version personal.xls???)

Option Explicit
Public WithEvents xlApp As Application
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Set xlApp = Nothing
End Sub
Private Sub Workbook_Open()
Set xlApp = Excel.Application
End Sub
Private Sub xlApp_WorkbookOpen(ByVal Wb As Workbook)
Dim NewFileName As String
NewFileName = Left(Wb.FullName, InStrRev(Wb.FullName, ".")) & "bak"
Wb.SaveCopyAs Filename:=NewFileName
End Sub


(Instrrev was added in xl2k. If you're using xl97, you'll have to parse the
filename differently.)

And an alternative...

Another option would be to dump autosave and use Jan Karel Pieterse's addin
(works in any version) called AutoSafe (note spelling).

It doesn't overwrite the existing workbook when it saves. It saves to a user
selectable folder. And when it's done, it either deletes these backups (or puts
them in the recycle bin). And the user can always restore the backups from the
recycle bin.

http://www.jkp-ads.com/Download.htm
(look for AutoSafe.zip, not autosafeVBE.zip, for your purposes.)

 

[avi]

Thank you very much. This looks very promising. I'm off to home now, so first
thing tomorrow I'll give this a go.

Arnold Vink

 

[avi]

Thank you very much. This looks very promising. I'm off to home now, so first
tomorrow I'll give this a go.

Arnold Vink

 

[avi]

You can use an application level event.

This goes in the ThisWorkbook module of your persnlk.xls workbook.

(persnlk.xls is the same as the USA version personal.xls???)

Option Explicit
Public WithEvents xlApp As Application
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Set xlApp = Nothing
End Sub
Private Sub Workbook_Open()
Set xlApp = Excel.Application
End Sub
Private Sub xlApp_WorkbookOpen(ByVal Wb As Workbook)
Dim NewFileName As String
NewFileName = Left(Wb.FullName, InStrRev(Wb.FullName, ".")) & "bak"
Wb.SaveCopyAs Filename:=NewFileName
End Sub


(Instrrev was added in xl2k. If you're using xl97, you'll have to parse the
filename differently.)

And an alternative...

Another option would be to dump autosave and use Jan Karel Pieterse's addin
(works in any version) called AutoSafe (note spelling).

It doesn't overwrite the existing workbook when it saves. It saves to a user
selectable folder. And when it's done, it either deletes these backups (or puts
them in the recycle bin). And the user can always restore the backups from the
recycle bin.

http://www.jkp-ads.com/Download.htm
(look for AutoSafe.zip, not autosafeVBE.zip, for your purposes.)

 

[Gord Dibben]

Just a note.............

Excel 2003 does not recognize *.bak files.

Backup files are *.xlk


Gord Dibben MS Excel MVP