Adding Date to File name

[nkechifesie]

Hi, I have written a VBA program that runs on Excel and puts data on the excel sheet. This runs everyday. I want to be adding the dates to the files, this date is gotten from the excel sheet that uploads into the report excel file. Below is the Code I wrote which doesnt work, please could you help me

    Sheets("Matrix sheet").Select
    Today = Cells(1, 1)       'The location of the date on the raw sheet
    Today = Format(Today, "dd-mm-yyyy")
    Windows("cells.xls").Close savechanges:=True, Filename:="c:\Daily_Alerts\Daily Alerts_ " & Today & " "

[Killer42]

Apart from the slightly questionable habit of using the same variable (Today) to hold data of two different formats (date and string) what seems to be the problem? I thought the code looked alright.

Hint: the problem is not "it doesn't work". You need to be specific. For example, have you stopped the code at the point of executing the Close and examined the string that is being supplied as the filename? What is the string? Is an error occuring? If so, what are the error details?

[nkechifesie]

I have resolved it. The issue was that .xls was not included in the file name. Thanks for all your help.
Below is the code that works now

Dim Today As Date
Dim Todayb As String
Today = Cells(1, 1)
Todayb = Format(Today, "dd-mm-yyyy")
Todayb = Todayb & ".xls"
Windows("cells.xls").Close savechanges:=True, Filename:="c:\Daily_Alerts\Daily Alerts_" & Todayb & " "

[Killer42]

Ok, glad to see you got it sorted out. Debugging usually ends up being about checking every little detail like that.

[chiz]

I am attempting to add a date to a file name. I want the first file to end with
30-june-07, and each file going forward to end with the end of the month. I keep receiving the 'Invalid procedure call ir argument' error. Here is my code:

Sheets("Portfolio").Select
    Sheets("Portfolio").Copy
    ChDir "P:\Conduits\Servicing\Port Stats\Allied\Archive\Portfolio"
    ActiveWorkbook.SaveAs Filename:= _
        "P:\Conduits\Servicing\Port Stats\Allied\Archive\Portfolio\Portfolio_" & DateAdd(m, 1, 30 - Jun - 7) & " ", FileFormat:= _
        xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False
    ActiveWindow.Close
    Sheets("WA").Select
    Sheets("WA").Copy
    ChDir "P:\Conduits\Servicing\Port Stats\Allied\Archive\WA"
    ActiveWorkbook.SaveAs Filename:= _
        "P:\Conduits\Servicing\Port Stats\Allied\Archive\WA\WA_" & DateAdd(m, 1, 30 - Jun - 7) & " ", FileFormat:= _
        xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
        , CreateBackup:=False
    ActiveWindow.Close
End Sub

Thanks for your help!

[Killer42]

You didn't say which line produced the error. However, I think this function call...
DateAdd(m, 1, 30 - Jun - 7)
probably has some problems. You are telling it to take 30, subtract some variable called Jun, then subtract 7, and treat the result as a date value. Somehow, I don't think this is what you intended.

You might (I haven't checked) get away with writing it this way, if you put # delimiters around your literal value. For example...
DateAdd(m, 1, #30-Jun-07#)

Another thing to consider is this. The DateAdd function will return a date value. You are then placing it in a string, which forces VB to convert it to a string. If you want it to be presented in a particular format (eg DD-MMM-YY) then you might need to use something like the Format() function to force it. However, you may be perfectly happy with your computer's default format, in which case don't worry about it. But, if you need to be certain the format won't change when run on someone else's system, you had best enforce your format.