|
Versions of documents
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" |
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 "Frepez" wrote in message ... 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" |
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 "Chip Pearson" wrote: 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 "Frepez" wrote in message ... 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" |
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. Frepez wrote: 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 "Chip Pearson" wrote: 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 "Frepez" wrote in message ... 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" -- Dave Peterson |
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 "Dave Peterson" wrote: 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. Frepez wrote: 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 "Chip Pearson" wrote: 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 "Frepez" wrote in message ... 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" -- 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. Frepez wrote: 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 "Dave Peterson" wrote: 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. Frepez wrote: 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 "Chip Pearson" wrote: 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 "Frepez" wrote in message ... 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" -- Dave Peterson -- Dave Peterson |
| All times are GMT +1. The time now is 02:16 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com