Adding Date to File Name on Save

[Guest]

Is there a way to save a workbook under a "new" name by automatically adding
the date when saving on exit? Idea is to ensure previous version is kept as
a back-up and allow other users of same file to access the most up-to-date
version.

Many thanks for any help.

David

 

[Jim Rech]

I don't believe you can force a workbook to be saved under another name
unless it is "read-only". As a developer you might add a button or keyboard
shortcut that runs a macro to do the save. The macro could always make the
current date part of the file's name. Something like:

ThisWorkbook.SaveAs "ABC" & Year(Now) & Month(Now) & Day(Now)

or

ThisWorkbook.SaveAs "ABC" & Year(Now) & Format(Month(Now),"00") &
Format(Day(Now),"00")

--
Jim Rech
Excel MVP
| Is there a way to save a workbook under a "new" name by automatically
adding
| the date when saving on exit? Idea is to ensure previous version is kept
as
| a back-up and allow other users of same file to access the most up-to-date
| version.
|
| Many thanks for any help.
|
| David
|

 

[Dave O]

Hi, David-
This is code I use to do that very chore. This program picks up
current timestamp information, saves the file with timestamp to an
archive folder, then saves the file to its typical location. If you
use this you'll need to view the code carefully to replace things like
"Your_File_Name" with the file's actual name.

Dave O

Sub Save_and_Archive()
Dim Munth As String, Deigh As String, Yeer As String, Our As String,
Minit As String

'Collect timestamp information
Munth = Month(Now)
If Len(Munth) = 1 Then Munth = "0" & Munth

Deigh = Day(Now)
If Len(Deigh) = 1 Then Deigh = "0" & Deigh

Yeer = Right(Year(Now), 2)

Our = Hour(Now)
If Len(Our) = 1 Then Our = "0" & Our

Minit = Minute(Now)
If Len(Minit) = 1 Then Minit = "0" & Minit

'Change the directory to the "archive" directory
'(where the timestamped file will be held)
ChDir "E:\Archive_Folder_Name"
ActiveWorkbook.SaveAs Filename:= _
"E:\Archive_Folder_Name\Your_File_Name " & Munth & Deigh &
Yeer & " " & Our & Minit & ".xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

'Delete the file from its typical location
Kill "C:\Typical_Folder\Your_File_Name.xls"

'Change the default directory to the file's typical folder
ChDir "C:\Typical_Folder"
ActiveWorkbook.SaveAs
Filename:="C:\Typical_Folder\Your_File_Name.xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
End
End Sub

 

[Guest]

Jim & Dave,

this is great guys. Just what I needed. many thanks.

David

 

[Dave Peterson]

Or...

ThisWorkbook.SaveAs "ABC" & Format(date,"yyyymmdd")

<vbg>