Versions of documents

F

Frepez

Hi I wonder if there is any way to make excel keep track of a version of a
document. I want it to be renamed everytime I save, preferably with todays
date.. For example "Book_22-03-2005.xls"
 
C

Chip Pearson

In the ThisWorkbook code module, paste the following code:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel
As Boolean)
Dim FName As String
FName = ThisWorkbook.Name
If Right(FName, 4) = ".xls" Then
FName = Left(FName, Len(FName) - 4)
End If
Application.DisplayAlerts = False
ThisWorkbook.SaveAs Filename:=FName & Format(Now, "dd-mm-yyyy") &
".xls"
Application.DisplayAlerts = True


End Sub



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
F

Frepez

Hi Chip, thanks for yopr input, but I don't quit get it to work. When I have
saved the document it ads todays date, but when I save it again it ads on the
date again so the file name ends up "book1dd-mm-yyyydd-mm-yyyy.xls

And then excel starts over because of some error.

Can you maybe get it to save with todays date and a version number after
instead as the example below.

"book1_dd-mm-yyyy_v1.xls" and the next time "book1_dd-mm-yyyy_v2.xls"

And the day after it starts ove with "book1_dd-mm-yyyy_v1.xls" and so on?

Best Regards
/Freddie
 
D

Dave Peterson

Maybe you could use something like this that looks for 10 characters that look
look like that date:

Option Explicit
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim FName As String

FName = ThisWorkbook.Name
If Right(FName, 4) = ".xls" Then
FName = Left(FName, Len(FName) - 4)
End If
If Right(FName, 10) Like "##-##-####" Then
FName = Left(FName, Len(FName) - 10)
End If

Application.DisplayAlerts = False
Application.EnableEvents = False
ThisWorkbook.SaveAs Filename:=FName & Format(Now, "dd-mm-yyyy") & ".xls"
Application.EnableEvents = True
Application.DisplayAlerts = True

End Sub

You could even add the time stamp at the end, but then you'd have to look for
those characters, too.
 
F

Frepez

Hi Dave, this seem to work fine but how can I get a time stamp? This is a
good Idea since I save the document several times per day.

When I press "save" or "save as" I want it to create a new file with the
date and the current time.

Thanks in advance

/Freddie
 
D

Dave Peterson

Just look for that same kind of string at the end:

Option Explicit
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim FName As String


FName = ThisWorkbook.Name

If Right(FName, 4) = ".xls" Then
FName = Left(FName, Len(FName) - 4)
End If

If Right(FName, 9) Like "-##-##-##" Then
FName = Left(FName, Len(FName) - 9)
End If

If Right(FName, 10) Like "##-##-####" Then
FName = Left(FName, Len(FName) - 10)
End If

Application.DisplayAlerts = False
Application.EnableEvents = False
ThisWorkbook.SaveAs _
Filename:=FName & Format(Now, "dd-mm-yyyy-hh-mm-ss") & ".xls"
Application.EnableEvents = True
Application.DisplayAlerts = True

End Sub

myfilename24-03-2005-12-25-33.xls

would be the format of the name of the file.
 
Top