PDA

View Full Version : save workbook titled with current date



photon_ed
05-20-2006, 10:06 AM
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




sub newWorkbook()

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

end sub

Norie
05-20-2006, 10:31 AM
Try this.

Sub newWorkbook()

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

End Sub

photon_ed
05-20-2006, 10:53 AM
Thanks Norie, That was what i needed :) however, any suggestion if it has to display the date of yesterday?
Thank you.

yours,
Ed

lucas
05-20-2006, 11:04 AM
to save it to the hard drive.....

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


to save as yesterdays date:

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

photon_ed
05-20-2006, 11:31 AM
Thanks for the prompt replies, they work perfectly :)
Thank you

yours,
Ed