Try some code like the following. It will save a copy of the file, not the
file itself, with the date and time following the file name (but before the
".xls"). Change the constant value C_DATETIME_FORMAT to the date and/or
time format you want to use in the file name. Note that you can't use a ":"
character to delimit the time.
The code will save "C:\Test\Book1.xls" as "C:\Test\date_and_time_Book1.xls".
Sub SaveCopyAsArchive()
Dim Path As String
Dim FName As String
Dim Extension As String
Dim LastSlashPos As Integer
Dim PeriodPos As Integer
Dim FullFileName As String
'<<<<<<< CHANGE BELOW TO DESIRED DATE/TIME FORMAT
Const C_DATETIME_FORMAT = "_hh\;mm\;ss_dd_mmm_yyyy"
If ActiveWorkbook.Path = vbNullString Then
MsgBox "You must save the file first."
Exit Sub
End If
FullFileName = ThisWorkbook.FullName
LastSlashPos = InStrRev(FullFileName, "\", -1, vbBinaryCompare)
PeriodPos = InStrRev(FullFileName, ".", -1, vbBinaryCompare)
Path = Left(FullFileName, LastSlashPos)
Extension = Mid(FullFileName, PeriodPos)
FName = Mid(FullFileName, LastSlashPos + 1, PeriodPos - LastSlashPos -
1)
FullFileName = Path & FName & Format(Now, C_DATETIME_FORMAT) & Extension
ThisWorkbook.SaveCopyAs Filename:=FullFileName
End Sub
--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)
