save workbook titled with current date

[photon_ed]

Hello,

simple question, I would like to save the "TESTING.xls" workbook as "TESTING 5/20/2006.xls" or "TESTING 5thMay2006". I tried using "I:\TESTING" & Date ".xls" and "I:\TESTING.xls" & Date, but obviously excel didnt like it and refuse to do so. Can someone please advise.
Thank you

Yours,
Ed


[VBA]

sub newWorkbook()

Workbooks.Add
ActiveWorkbook.SaveAs Filename:= _
"I:\TESTING.xls", FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False

end sub
[/VBA]

[Norie]

Try this.
[vba]
Sub newWorkbook()

Workbooks.Add
ActiveWorkbook.SaveAs Filename:="I:\TESTING " & Format(Date, "ddmmmyyyy") & _
".xls", FileFormat:=xlNormal

End Sub
[/vba]

[photon_ed]

Thanks Norie, That was what i needed however, any suggestion if it has to display the date of yesterday?
Thank you.

yours,
Ed

[lucas]

to save it to the hard drive.....
[VBA]
Sub saveas()
Dim newFile As String, fName As String
' Don't use "/" in date, invalid syntax
fName = "TESTING"
newFile = fName & " " & Format$(Date, "mm-dd-yyyy")
' Change directory to suit
ChDir _
"F:\Temp"
ActiveWorkbook.saveas Filename:=newFile
End Sub
[/VBA]

to save as yesterdays date:
[VBA]
Sub saveas()
Dim newFile As String, fName As String
' Don't use "/" in date, invalid syntax
fName = "TESTING"
newFile = fName & " " & Format$(Date - 1, "mm-dd-yyyy")
' Change directory to suit
ChDir _
"F:\Temp"
ActiveWorkbook.saveas Filename:=newFile
End Sub
[/VBA]

[photon_ed]

Thanks for the prompt replies, they work perfectly
Thank you

yours,
Ed