Tuesday, August 5, 2014

Date and Date-Time Stamping Files


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


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?


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
                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
        ' 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"
            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
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
            booFileHasDateTimeStamp = False
        End If
        booFileHasDateTimeStamp = False
    End If
End Function

No comments:

Post a Comment