IntroductionThis is not something new to me. I worked this out a few years ago, but when Allen asked for help for someone else on this, I dusted off the old code, added a bell and 2 whistles (or was it a whistle and two bells?)
Oh, the question! To paraphrase, "How do I save files with the current date in the file name?"
- Would a date-time stamp be useful in that there may be a need to save during the work day, carry on, then save again when the last slice of pizza is gone?
- Is this a series of files, each with a later and later date/date-time stamp in which the newest one is the work in process and the others rollback points?
- Is this a single file in which date/date-time stamped files, also useful as rollback points are dropped off from time to time?
Dim strFileRootName As String
Dim strExt As String
Const strExtMin As String = ".xl"
' Const strDateTimeStampFormat As String = " yyyy-mm-dd hh\hmm\mss\s" ' Date & Time
Const strDateTimeStampFormat As String = " yyyy-mm-dd" ' Date only
strFileRootName = FileNameNoDate(ActiveWorkbook.Name)
strExt = Right(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - InStr(1, ActiveWorkbook.Name, strExtMin) + 1)
strFileRootName & Format(Now(),strDateTimeStampFormat ) & strExt ' Save the archive
ActiveWorkbook.SaveAs strFileRootName & strExt ' Returns to original name
For those interested, here is the link to the answer on Allen's website. There are a couple of alternatives offered from other readers as well
The bigger solution I devised, one that addresses the question above, is a Subroutine and an accompanying Function.
Assumptions and Limitations
- The date/date-time stamp is the rightmost part of the filename, with the exception of the extension, e.g ".xls" or "xlsm"
- The date/date-time stamp should separated by a space from the core file name for ease of reading.
N.B. The core is what the file would be named with out the date/date-time stamp and the extension.
- In Windows explorer, it is useful to sort file names from most recent to oldest or oldest to newest while keeping all files with the same core file name. This implies
- Ordering the elements from coarsest unit of measure to finest: year, month number, day/date, hours in 24 hour mode, minutes and second.
- Using numbers instead of names for months.
- Using zero file, e.g. March = "03" instead of "3"
- Data and time elements should be visibly connected yet the boundaries between them consistent and meaningful. This assists the user in scanning the date/date-time stamp.
- Colons, often used for separating levels of time, e.g. hours v minutes v seconds, and slashes, often used to separate the date elements, cannot be used in file names
- Characters linking date and time elements assist the user in reading the date/datetime stamp.
- The use of periods to separate time elements, e.g. 14.22.16, was rejected in favor of "H" or "h" for hours, "M" or "m" for minutes and "S" or "s" for seconds.
- Therefore the date/date-time stamp format begins as " yyyy-mm-dd hh'h'mm'm'ss's'". Example: 2012-11-04 14h22m16s" but can be truncated to end at minutes or hours.
- A file without a date/date-time stamp is meant to have a date/date-time stamp file created but the original file is the one to use. The implied meaning of this approach is the changes were made in the file up to the date/date-time stamp.
- A file with a date/date-time stamp will have a final version save and all new work will be in the new file with a new date/date-time stamp. The implied meaning of this approach is the changes made in the file occurred after the date/date-time stamp.
- The code will be run with the Active Workbook as the key object.
- The Subroutine, DateTimeStampSave, does the following
- parses the file name, separating the extension from the