Add date to Excel file name

[Phil1122]

Hi,

I usually save Excel files with the date included in the file name.

For example - "workbook name - 5Jun13.xls"


Is there a way to automatically include the date in the file name when I save?


thanks,

Phil

 

[shrivallabha]

Welcome to Chandoo.org forums.


This can be done using VBA but it will create a new file for every day so after some days you'll have 'n' number of files based on 'n' number of days saved. Is that what you want to achieve?

 

[SirJB7]

Hi, Phil122!


Place this code in the ThisWorkbook object section (Alt-F11 from Excel, and then in the left tree view panel):

-----

[pre]

Option Explicit

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
' constants
Const ksSep = " - "
Const ksFormat = "ddmmmyy"
Const ksDot = "."
Const ksPattern = ksSep & "##???##"
' declarations
Dim sNameExt As String, sName As String, sExt As String, sDate As String
Dim sSep As String, sSepDate As String
Dim I As Integer
' start
Application.EnableEvents = False
sNameExt = ThisWorkbook.Name
' process
I = InStr(StrReverse(sNameExt), ksDot)
sName = Left(sNameExt, Len(sNameExt) - I)
sExt = Right(sNameExt, I)
sDate = Right(sName, Len(ksFormat))
sSep = Right(Left(sName, Len(sName) - Len(sDate)), Len(ksSep))
sSepDate = Right(sName, Len(ksSep) + Len(sDate))
If sSepDate Like ksPattern Then sName = Left(sName, Len(sName) - Len(sSep) - Len(sDate))
sName = sName & ksSep & Format(Now(), ksFormat)
sNameExt = sName & sExt
ThisWorkbook.SaveAs sNameExt
' end
Cancel = True
Application.EnableEvents = True
End Sub

[/pre]
-----


Just advise if any issue.


Regards!