PDA

View Full Version : Solved: Save a document with today's date in a macro



sb003848
08-06-2009, 06:27 AM
Hello,

I have a macro that make many changes to a file. Before I run the macro, I would like to save a copy of the document in a specific folder with today's date or at least with a different filename that a previously saved version (we need to keep every old versions of the files).

Thanks for your help!!!

JKwan
08-06-2009, 07:11 AM
try this

sFilename = Month(Now) & "-" & Day(Now) & "-" & Year(Now)

MarkNumskull
08-06-2009, 07:31 AM
You could also try this, it would save it as todays date and would put Report infront of it, you can change this name to anything you want.

ActiveWorkbook.SaveAs ("Report" & Format(Now, "DD-MM-YYYY") & ".xls")

sb003848
08-06-2009, 07:35 AM
JKwan, thank you for the code... Got a few extra questions:

How can I assign the exact folder in which the file needs to be saved???

Can I also add the time (in case I need to save the file a second time in the same day)???

If I would like to add a name in front of the date/time, how should I include this in the code???

mdmackillop
08-06-2009, 07:39 AM
Consider filing by Year/Month/Date if you wish to view the files by date order in Explorer

ActiveWorkbook.SaveAs ("C:\AAA\Report " & Format(Now, "YYYY-MM-DD HH_MM_SS") & ".xls")

sb003848
08-06-2009, 08:30 AM
ActiveWorkbook.SaveAs ("C:\AAA\Report " & Format(Now, "YYYY-MM-DD HH_MM_SS") & ".xls")

This works great... The only little detail is the file name:
Report 2009-08-06 11_25_21

I removed the SS to make the file name a bit shorter but is there a way to have it show up like one of the 2 examples mentionned bellow???
Report 2009-08-06 11h25
Report 2009-08-06 11:25

sb003848
08-06-2009, 09:04 AM
It's good... I found it:

ActiveWorkbook.SaveAs ("C:\AAA\Report " & Format(Now, "YYYY-MM-DD HH") & "h" & Format(Now, "MM") & ".xls")

mdmackillop
08-06-2009, 09:09 AM
The second MM may be Month. Best to check

sb003848
08-06-2009, 09:17 AM
Damn... You're right, it did use the Month instead of minutes...

But I think I'll forget about the hours/minutes and just save over the old file with the same date... I just noticed the macro will "pause", then ask you to if you wanna replace or not the file and then continue... That will be enough for me (or my boss)!!!

mdmackillop
08-06-2009, 09:23 AM
Check out this post (http://vbaexpress.com/forum/showthread.php?t=27927)for incrementing version numbers