Auto save File name with date/time

[Dbuilder6]

Hello all,

I am attempting to auto save the file name followed by date/time with each use of my excel file.

I've found the following code which I can get it to work on the first save, but I can't get it to renew the time/date in the new file name on subsequent saves/versions.

Sub AddTimestampToFileName()
'Updated by Extendoffice 20191223
Dim xWb As Workbook
Dim xStr As String
Dim xStrOldName As String
Dim xStrDate As String
Dim xFileName As Variant
Dim xFileDlg As FileDialog
Dim i As Variant
Application.DisplayAlerts = False
Set xWb = ActiveWorkbook
xStrOldName = xWb.Name
xStr = Left(xStrOldName, Len(xStrOldName) - 5)
xStrDate = Format(Now, "yyyy-mm-dd hh-mm-ss")
If Right(xStrOldName, 4) = "xlsm" Then
xFileName = Application.GetSaveAsFilename(xStr & " " & xStrDate, "Excel Macro-Enabled Workbook (*.xlsm),*.xlsm")
Else
xFileName = Application.GetSaveAsFilename(xStr & " " & xStrDate, "Excel Workbook (*.xlsx),*.xlsx")
End If
If xFileName = False Then
Else
xWb.SaveAs (xFileName)
End If
Application.DisplayAlerts = True
End Sub

 

[gordsky]

are you wanting to save the file in the same path with same name and file extension but also with date and time at end.? If so try this

Sub AddTimestampToFileName()
Dim wb As Workbook
Dim MySave As String
Set wb = ThisWorkbook
MySave = Split(ThisWorkbook.FullName, ".")(0) & " " & Format(Now(), "yyyy-mm-dd hh-mm-ss") & "." & Split(ThisWorkbook.FullName, ".")(1)

wb.SaveAs (MySave)
End Sub

 

[Dbuilder6]

are you wanting to save the file in the same path with same name and file extension but also with date and time at end.? If so try this

Sub AddTimestampToFileName()
Dim wb As Workbook
Dim MySave As String
Set wb = ThisWorkbook
MySave = Split(ThisWorkbook.FullName, ".")(0) & " " & Format(Now(), "yyyy-mm-dd hh-mm-ss") & "." & Split(ThisWorkbook.FullName, ".")(1)

wb.SaveAs (MySave)
End Sub

Thanks so much gordsky! I'm new to VBA. Do I add this to the end of what I have, in substitution for part of what I have, or rewrite part of what I have? As in: where do I put it. Thanks again!

 

[Dbuilder6]

To clarify; Each time I open up the last saved workbook, I'd like to hit save and have it auto update the date/time, but save in the same file. Just an updated workbook.

 

[gordsky]

I would put it as a substitution, I think it does everything your old code did. You cant rename an open workbook so you will always generate a new copy of the workbook. My code will save it to the same path as the existing