0

I have been playing with the following code. It works the first time, but then it appends another timestamp on the filename that already was timestamped before. I would like this to create a new filename in the same directory with format 2018 Test_YYYY.MM.DD_HHMM with the timestamp updated each time. Bonus if i can figure out how to add the last user who modified the file!

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim sFileName As String
    Dim sDateTime As String

    With ThisWorkbook
        sDateTime = " (" & Format(Now, "yyyy-mm-dd hhmm") & ").xlsm"
        sFileName = Application.WorksheetFunction.Substitute _
          (.FullName, ".xlsm", sDateTime)
        .SaveCopyAs sFileName
    End With
End Sub
Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
JR2018
  • 1
  • 1

1 Answers1

2

This should determine whether you have already added a datetime to the file name.

Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim sDateTime As String, sFileName As String

    With ThisWorkbook
        sDateTime = " (" & Format(Now, "yyyy-mm-dd hhmm") & ").xlsm"
        If Right(LCase(.FullName), 6) = ").xlsm" Then
            sFileName = Left(.FullName, Len(.FullName) - Len(sDateTime)) & sDateTime
        Else
            sFileName = Left(.FullName, InStrRev(.FullName, ".") - 1) & sDateTime
        End If
        .SaveCopyAs sFileName
    End With
End Sub
  • I got this far. I simplified the code to just have a default name and add a timestamp to it. That works well. Now I am trying to create a Save As Dialog that pops up to – JR2018 Mar 18 '18 at 12:54