Writing Macro To Save Excel File with New dates each time 1

[twkyoscr]

I am not too savvy with VB but I am trying to create a macro that will save a file to a new folder. My problem is, each time the file name will be different(because of a new date) and I do not know how to write the macro so that it will copy the most recent file name without replacing a previous file. See below for what I have created.

"S:\HRShare\WorkforcePlanning\Suzanne\Riley-Posted Positions by Recruiter"
ActiveWorkbook.SaveAs FileName:= _
"S:\HRShare\WorkforcePlanning\Suzanne\Riley-Posted Positions by Recruiter\Riley-Posted Positions by Recr2004-05-06.xls" _
, FileFormat:=xlExcel9795, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
End Sub

Any help would be greatly appreciated!!

 

[Loomah]

Hi
Using an extract of what you have already, create a variable to contain the date - I'm assuming today in yyyy-mm-dd format

myDate = format(now,"yyyy-mm-dd")

ActiveWorkbook.SaveAs FileName:= _
"S:\HRShare\WorkforcePlanning\Suzanne\Riley-Posted Positions by Recruiter\Riley-Posted Positions by Recr" & myDate & ".xls"

should do the trick
;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[twkyoscr]

I see what you are saying with this piece
ActiveWorkbook.SaveAs FileName:= _
"S:\HRShare\WorkforcePlanning\Suzanne\Riley-Posted Positions by Recruiter\Riley-Posted Positions by Recr" & myDate & ".xls"

however I am not sure where to place the statement:

myDate = format(now,"yyyy-mm-dd")

Can you further explain? Thanks for your help!

 

[Loomah]

Yeah
Basically the statement needs to be where it is in my example ie before the workbook.saveas line. It could be the first line of your routine or it could be immediately before calling on the save method - it doesn't matter where, precisely!

If you call the saveas method before assigning a value to myDate then you will save the file without a date at all. Test it! You'll end up with a file called "Riley-Posted Positions by Recr.xls"

You can assign any value to myDate as long as you can ultimately recognise it the way you want to. I used to use dates in the format "yymmdd". I've used the Now() function to get today's date, by way of example, but if you want to use a date in a cell in a worksheet that would work just the same.

Clear as mud?!?

;-)


If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[twkyoscr]

Thank you so much for the example and the explanation. You have saved me a ton of time and trouble!! This works perfectly!