ExcelBanter - How do I copy a file and attach a date to the name of the file

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)

"hccatmo" wrote in message
...
Hi there,
I'd like to routinely copy a xls file to a location (for archive) thus
want
to attach/append the date to the name of the file.
Thx.
cg