Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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" |
#2
|
|||
|
|||
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" |
#3
|
|||
|
|||
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" |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Error: Cannot open two documents with the same name | Excel Discussion (Misc queries) | |||
Why do my excel documents not open from the desktop? | Excel Discussion (Misc queries) | |||
Need list of the different versions of Excel | Excel Discussion (Misc queries) | |||
Recent Documents Disappear (Excel / Office 2000) | Excel Discussion (Misc queries) | |||
Excel opens all files in My Documents on launch | New Users to Excel |