Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I copy a file and attach a date to the name of the file
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
attach a file to a cell in a spreadsheet | Excel Discussion (Misc queries) | |||
Saving worksheet in new file with date AND cell value as file name | Excel Discussion (Misc queries) | |||
can we attach an image file to a excel cell ,like note pad comment | New Users to Excel | |||
copy excell file to a CSV file and than to Notepad | Excel Discussion (Misc queries) | |||
copy/paste from one file to another without file name reference | Excel Discussion (Misc queries) |