Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving File with name and date
Is there a way to save the file I am working on (template) with the name of
the file and the date? ex. "filename - 2008-10-29.xls" also, if the name is duplicated, add a number to the end numerically ex. "filename - 2008-10-29-01.xls" Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving File with name and date
You could adapt the following to suit......it creates files in the
following format if already in the same path filename - 2008-10-29.xls filename - 2008-10-29(1).xls filename - 2008-10-29(2).xls etc..... Dim myPath As String, myFile As String, myExt As String, mySerial As String mySerial = "" myPath = "C:\Test\" myFile = "filename" & " - " & Format(Date,"YYYY-MM-DD") myExt = ".xls" ' create output using sequence 1 to n if file already exists If Len(Dir(myPath & myFile & mySerial & myExt)) 0 Then Do While Len(Dir(myPath & myFile & mySerial & myExt)) 0 mySerial = "(" & Val(Mid(mySerial, 2)) + 1 & ")" Loop End If ActiveWorkbook.SaveAs Filename:=myPath & myFile & mySerial & myExt -- Regards, Nigel "Novice Lee" wrote in message ... Is there a way to save the file I am working on (template) with the name of the file and the date? ex. "filename - 2008-10-29.xls" also, if the name is duplicated, add a number to the end numerically ex. "filename - 2008-10-29-01.xls" Thanks |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving File with name and date
I had the same question before and tried different things. I tried what you
said for one of my files, and it worked. Just one question: Can I use this in a Workbook_Open event where after I save it using your code, to delete the code from the new file? I currently have this in my PERSONAL.xls file and can call it from a custom menu. I would like to reduce errors this way and not freak others out with the macro. -- I am running on Excel 2003, unless otherwise stated. "Nigel" wrote: You could adapt the following to suit......it creates files in the following format if already in the same path filename - 2008-10-29.xls filename - 2008-10-29(1).xls filename - 2008-10-29(2).xls etc..... Dim myPath As String, myFile As String, myExt As String, mySerial As String mySerial = "" myPath = "C:\Test\" myFile = "filename" & " - " & Format(Date,"YYYY-MM-DD") myExt = ".xls" ' create output using sequence 1 to n if file already exists If Len(Dir(myPath & myFile & mySerial & myExt)) 0 Then Do While Len(Dir(myPath & myFile & mySerial & myExt)) 0 mySerial = "(" & Val(Mid(mySerial, 2)) + 1 & ")" Loop End If ActiveWorkbook.SaveAs Filename:=myPath & myFile & mySerial & myExt -- Regards, Nigel "Novice Lee" wrote in message ... Is there a way to save the file I am working on (template) with the name of the file and the date? ex. "filename - 2008-10-29.xls" also, if the name is duplicated, add a number to the end numerically ex. "filename - 2008-10-29-01.xls" Thanks |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving File with name and date
Thanks that exactly what I wanted
"Nigel" wrote: You could adapt the following to suit......it creates files in the following format if already in the same path filename - 2008-10-29.xls filename - 2008-10-29(1).xls filename - 2008-10-29(2).xls etc..... Dim myPath As String, myFile As String, myExt As String, mySerial As String mySerial = "" myPath = "C:\Test\" myFile = "filename" & " - " & Format(Date,"YYYY-MM-DD") myExt = ".xls" ' create output using sequence 1 to n if file already exists If Len(Dir(myPath & myFile & mySerial & myExt)) 0 Then Do While Len(Dir(myPath & myFile & mySerial & myExt)) 0 mySerial = "(" & Val(Mid(mySerial, 2)) + 1 & ")" Loop End If ActiveWorkbook.SaveAs Filename:=myPath & myFile & mySerial & myExt -- Regards, Nigel "Novice Lee" wrote in message ... Is there a way to save the file I am working on (template) with the name of the file and the date? ex. "filename - 2008-10-29.xls" also, if the name is duplicated, add a number to the end numerically ex. "filename - 2008-10-29-01.xls" Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
saving a file with a date | Excel Worksheet Functions | |||
Saving worksheet in new file with date AND cell value as file name | Excel Discussion (Misc queries) | |||
saving file name with the date | Excel Programming | |||
Saving file with date on it | Excel Programming | |||
Saving file as a date | Excel Programming |