Saving File with name and date

[Novice Lee]

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

 

[Nigel]

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

 

[Orion Cochrane]

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.

 

[Novice Lee]

Thanks that exactly what I wanted

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
(e-mail address removed)