Date and Date-Time Stamping Files

Introduction

This 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?"

Analysis

This question makes sense if someone is doing this everyday at the end of using the file.  It's creating an archive, at least that's the way I see it.

However, to take it a bit further I thought these things should be considered

• 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?

Solutions

The simple solution I devised, the one to the question asked, is fairly simple

Sub SaveWithNewDate()
    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)
    
    ActiveWorkbook.SaveAs _
        strFileRootName & Format(Now(),strDateTimeStampFormat  ) & strExt  ' Save the archive
    ActiveWorkbook.SaveAs strFileRootName & strExt ' Returns to original name


End Sub

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 Code

As mentioned above, the solution consists of a single subroutine and an accompanying function.

• The Subroutine, DateTimeStampSave, does the following
• parses the file name, separating the extension from the 

Sub DateTimeStampSave()

    Dim strFileRootName As String

    Dim strExt As String

    Const strExtMin As String = ".xl"

    Const strDateFormat As String = " yyyy-mm-dd"

    Const strDateTimeFormat As String = " yyyy-mm-dd hh\hnn\mss\s"

             ' The backslashes act as excape characters, causing the following character to be treated as literlal

    Dim strFormatToUse As String

    Dim InpReply As String

    strExt = Right(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - InStr(1, ActiveWorkbook.Name, strExtMin) + 1)

    strFileRootName = Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - Len(strExt))

    If booFileHasDateTimeStamp(strFileRootName, strDateFormat) _

        Or booFileHasDateTimeStamp(strFileRootName, strDateTimeFormat) Then

        ' This part of the IF assumes that a file with a date or date-time stamp will be created and from this point forward the file with _

        new name will be the one in use.  In this case, the date/date-time stamp is the equivalent of a Created On

            If booFileHasDateTimeStamp(strFileRootName, strDateFormat) Then

                strFormatToUse = strDateFormat

            Else

                strFormatToUse = strDateTimeFormat

            End If

            strFileRootName = Trim(Left(strFileRootName, Len(strFileRootName) - Len(Replace(strTimeDateFormat, "\", ""))))  ' Eliminates the old date/time-date stamp

            ActiveWorkbook.Save ' Saves the final changes to the current file

            ActiveWorkbook.SaveAs _

                ActiveWorkbook.Path & "\" & strFileRootName & Format(Now(), strFormatToUse) & strExt

    Else

        ' The ELSE assumes that a file saved with a date-time stamp from one without one is an archive _

            or check point file. The original filename is continued in use. _

            In this case, the date/date-time stamp is the equivalent of "Closed On"

        Do

            InpReply = UCase(InputBox("Enter 'D' for date stamping, 'T' for date & time stamping, 'N' for neither" _

                                & Chr(10) & "Leave blank or click Cancel to not save the file", "Date/Stime Stamp Save"))

        Loop Until Trim(InpReply) = "D" Or InpReply = "T" Or InpReply = "N" Or InpReply = ""

        Select Case Trim(InpReply) ' Trim used for inadvertent spaces

            Case "" ' This occurs with Cancel or blank and OK

                GoTo DateTimeStampSave_EXIT

            Case "D"

                strFormatToUse = strDateFormat

            Case "T"

                strFormatToUse = strDateTimeFormat

            Case "N"

                ActiveWorkbook.Save ' No file with new date/date-time stamp

                GoTo DateTimeStampSave_EXIT

        End Select

        ActiveWorkbook.SaveAs _

            ActiveWorkbook.Path & "\" & strFileRootName & Format(Now(), strFormatToUse) & strExt

        Application.DisplayAlerts = False

        ActiveWorkbook.SaveAs _

            ActiveWorkbook.Path & "\" & strFileRootName & strExt ' Returns to original name

        Application.DisplayAlerts = True

    End If

DateTimeStampSave_EXIT:

End Sub

Function booFileHasDateTimeStamp(strFileRootName As String, strTimeDateFormat As String) As Boolean

    Dim intDateTimeFormatLength As Integer

    Dim strPossibleDateTimeStamp As String

    

    Const strExtMin As String = ".xl"

    intDateTimeFormatLength = Len(Trim(Replace(strTimeDateFormat, "\", "")))

    If Len(strFileRootName) > intDateTimeFormatLength Then

        strPossibleDateTimeStamp = Right(strFileRootName, intDateTimeFormatLength) 'Len(strFileRootName) -

        strPossibleDateTimeStamp = _

            Replace(Replace(Replace(UCase(strPossibleDateTimeStamp), "H", ":"), "M", ":"), "S", ":")

        If Right(strPossibleDateTimeStamp, 1) = ":" Then _

            strPossibleDateTimeStamp = Left(strPossibleDateTimeStamp, Len(strPossibleDateTimeStamp) - 1)

        If InStr(1, strPossibleDateTimeStamp, ":") = 0 Then _

            strPossibleDateTimeStamp = strPossibleDateTimeStamp & ":00" ' this is used if only the date-time stamp ends at the Hour level

        If IsDate(strPossibleDateTimeStamp) Then

            booFileHasDateTimeStamp = True

        Else

            booFileHasDateTimeStamp = False

        End If

    Else

        booFileHasDateTimeStamp = False

    End If

End Function