Save a bakup file when opening the file

[Mijn_Newsgroepen]

When i open a certain file i want that immediately a backup of this file is
saved to the same folder but with the date that the file was opened added.

Example : file name is "file1.xls".
When this file is opened on 16-dec, i want a backup saved under the name
"file1-16-dec-2010.xls"
When the file is opened a second or third,... time on the same day, then the
backup file "file1-16-dec-2010.xls"has to be overwritten whitout asking


Who can provide me with some code?

Thanks,

Luc

 

[Per Jessen]

This is an event code which has to be inserted in ThisWorkbook code
sheet:

Private Sub Workbook_Open()
Dim fName As String
Dim MyDate As String

fName = ThisWorkbook.Name
fName = Left(fName, Len(fName) - 4)
MyDate = Format(Date, "dd-mmm-yyyy")
Application.DisplayAlerts = False
ThisWorkbook.SaveAs fName & "-" & MyDate & ".xls"
Application.DisplayAlerts = True
End Sub

 

[Don Guillett Excel MVP]

This is an event code which has to be inserted in ThisWorkbook code
sheet:

Private Sub Workbook_Open()
Dim fName As String
Dim MyDate As String

fName = ThisWorkbook.Name
fName = Left(fName, Len(fName) - 4)
MyDate = Format(Date, "dd-mmm-yyyy")
Application.DisplayAlerts = False
ThisWorkbook.SaveAs fName & "-" & MyDate & ".xls"
Application.DisplayAlerts = True
End Sub





- Show quoted text -

You may prefer to use save copy AS. Here is one I have used. Modify to
suit

Sub Backup() 'kept in personal.xls & assigned to toolbar button
On Error GoTo BackupFile
MkDir CurDir & "\Backup"
BackupFile:
With ActiveWorkbook
MyWB = .Path & "\BACKUP\" & .Name
.SaveCopyAs MyWB
.Save
End With
End Sub

 

[Luke]

Thanks mates,

I've combined your replys in something that works for my situation

Luc