By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,600 Members | 1,666 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,600 IT Pros & Developers. It's quick & easy.

Adding Date to File name

P: 62
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

Expand|Select|Wrap|Line Numbers
  1.     Sheets("Matrix sheet").Select
  2.     Today = Cells(1, 1)       'The location of the date on the raw sheet
  3.     Today = Format(Today, "dd-mm-yyyy")
  4.     Windows("cells.xls").Close savechanges:=True, Filename:="c:\Daily_Alerts\Daily Alerts_ " & Today & " "
  5.  
Jul 4 '07 #1
Share this Question
Share on Google+
5 Replies


Expert 5K+
P: 8,434
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?
Jul 5 '07 #2

P: 62
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

Expand|Select|Wrap|Line Numbers
  1. Dim Today As Date
  2. Dim Todayb As String
  3. Today = Cells(1, 1)
  4. Todayb = Format(Today, "dd-mm-yyyy")
  5. Todayb = Todayb & ".xls"
  6. Windows("cells.xls").Close savechanges:=True, Filename:="c:\Daily_Alerts\Daily Alerts_" & Todayb & " "
  7.  
Jul 6 '07 #3

Expert 5K+
P: 8,434
Ok, glad to see you got it sorted out. Debugging usually ends up being about checking every little detail like that.
Jul 8 '07 #4

P: 1
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:

Expand|Select|Wrap|Line Numbers
  1. Sheets("Portfolio").Select
  2.     Sheets("Portfolio").Copy
  3.     ChDir "P:\Conduits\Servicing\Port Stats\Allied\Archive\Portfolio"
  4.     ActiveWorkbook.SaveAs Filename:= _
  5.         "P:\Conduits\Servicing\Port Stats\Allied\Archive\Portfolio\Portfolio_" & DateAdd(m, 1, 30 - Jun - 7) & " ", FileFormat:= _
  6.         xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False
  7.     ActiveWindow.Close
  8.     Sheets("WA").Select
  9.     Sheets("WA").Copy
  10.     ChDir "P:\Conduits\Servicing\Port Stats\Allied\Archive\WA"
  11.     ActiveWorkbook.SaveAs Filename:= _
  12.         "P:\Conduits\Servicing\Port Stats\Allied\Archive\WA\WA_" & DateAdd(m, 1, 30 - Jun - 7) & " ", FileFormat:= _
  13.         xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
  14.         , CreateBackup:=False
  15.     ActiveWindow.Close
  16. End Sub
Thanks for your help!
Aug 10 '07 #5

Expert 5K+
P: 8,434
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.
Aug 11 '07 #6

Post your reply

Sign in to post your reply or Sign up for a free account.