Excel Macro - saveas command with current date?

I'm new to VB and am trying to code up a macro for excel. What I am hoping to do is to save a file in two locations. The getting them to save isn't a problem. However, what I would like to do is have one of the saves to have the current date at the end...

ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\ca5ms\Desktop\BAS05980.P.EFT.EOIIN.UNUMLIF.xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\ca5ms\Desktop\BAS05980.P.EFT.EOIIN.UNUMLIF.current date.xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
End Sub

Is there a way to make this happen I tried looking in general help files but I couldn't find anything that addressed that. I thought maybe if I stuck CDate in there that it might work, but it didn't. Thank you in advance for any help you can give.

Answer this question

Excel Macro - saveas command with current date?

Echo

Moved to VBA
PMNJ
Now.day etc. gives a run-time error.

The correct use of the Now function to return day, month and year is Day(Now()), Month(Now()) and Year(Now()).

I would use something more efficient than that though, and use the Format function, e.g. Format(Now(), "dd-mm-yyyy")

So you could have:

Sub SaveFileWithDate()
Dim strWBOnly As String 'workbook path and name without ".xls"
Dim strSaveWithDate As String
Dim strWBFullName As String

strWBFullName = ActiveWorkbook.FullName
strWBOnly = Left(strWBFullName, Len(strWBFullName) - 4)

strSaveWithDate = strWBOnly & "." & Format(Now(), "dd-mm-yyyy") & ".xls"

ActiveWorkbook.SaveAs FileName:=strSaveWithDate, FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False
End Sub
Ken Holcombe

Here is a simple way of doing it. A better approach might be to make a small function that will generate a valid filename but this should work. You might also want to add some code to make sure the file you're trying to write doesn't already exist.

ActiveWorkbook.SaveAs Filename:= _
        "C:\Documents and Settings\ca5ms\Desktop\BAS05980.P.EFT.EOIIN.UNUMLIF.xls", _
        FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
        ReadOnlyRecommended:=False, CreateBackup:=False
    ActiveWorkbook.SaveAs Filename:= _
        "C:\Documents and Settings\ca5ms\Desktop\BAS05980.P.EFT.EOIIN.UNUMLIF." & now.day & now.month & now.year & ".xls", _
        FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
        ReadOnlyRecommended:=False, CreateBackup:=False

I'm relatively new on the forums but I think you should post questions like this (VBA) to a different group...you might get a quicker / more accurate response.

http://forums.microsoft.com/MSDN/ShowForum.aspx ForumID=74&SiteID=1

Hope this helps!

Christopher
Cybergenius
current date can be generated by:

"C:\etc" & Format(Now(), "mmddyy")

just did this in a spreadsheet and concatenated with a last name field...tres cool.

dont use slashes or any other punctuation.
Excel Macro - saveas command with current date?

Answer this question