Date and Timestamp in Filename

  • Thread starter Thread starter Emily
  • Start date Start date
E

Emily

I have a spreadsheet that is modified several times a day
and by several people.
Is it possible to somehow insert the date and timestamp
(perhaps with a macro?) into the filename?
It is very difficult to get users to use
abbreviations/version numbers etc to show when the last
modified spreadsheet was used - many "forget".
If there was a way that automatically put the date and
time into the filename then users will know which is the
most recent document to use - and not have any excuses!
many thanks peeps!
 
Emily,

Pretty easy

ActiveWorkbook.SaveAs Filename:=sFilename & Format(Now,"yyyy mm dd
hh:mm:ss")

Don't put \/?; etc in the date, it will error if you do.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Hi Bob

You're quite the guru having looked through the posts!
Lucky I got you to answer my question!
Sorry to be a headache but I have only used Excel in the
traditional data input sense and have no ideas about
macros.
Could you explain to me what I need to do? - sorry *blush*
thanks
Emily
 
Emily,

Pretty easy

ActiveWorkbook.SaveAs Filename:=sFilename & Format(Now,"yyyy mm dd
hh:mm:ss")

Don't put \/?; etc in the date, it will error if you do.
Click to expand...
^^^

I think the list of invalid characters in filenames is
\ / : * ? " " < > |
so those colons you suggest fall under "etc" and wouldn't work.

Not strictly on the topic of the OP, but: additionally, there are many
other characters which might bite you if used in filenames:
` ! @ # $ % ^ & + ; ' ,
and any umlaut, accented or Unicode characters.
 
Thanks Michael, I was wondering about what that "V" myself,
hadn't occurred to that was supposed to be \ / etc.

I would put hyphens into the date so that they look like
ISO formatted dates.
"string_" & Format(Now(), "yyyy-mm-dd")
or something less obvious like
"c:\mybackup\B" & Format(Now(), "yyyy_mmdd")
spaces can be troublesome in filenamess.

As Bob was indicating but didn't give a reason, it is important to
place the year first so you can sort on filename. Same applies
to naming worksheets with a date so you can sort them
http://www.mvps.org/dmcritchie/excel/buildtoc.htm#sortallsheets
 
Emily,

Best thing to do is to create a toolbar button, and assign the macro I give
here to it.

First the macro.

Go into the VB IDE (Alt-F11)
Select your workbook from the list in the explorer window on the left
Insert a new code module (Insert>Module)
Paste the macro below in

Now the toolbar button
Goto Tools>Customize
On the Commands tab, select the Macros option
Drag and drop the smiley icon onto one of the existing toolbars
Right-click on the newly added toolbar button, select Assign Macro, and
choose the 'SaveFile' macro
Now close the Customize dialog

That should be it. Now when you click on Smiley it will save the
activeworkbook with '(TS date time)' prefix.

Here's the macros

Sub SaveFile
Dim sFile As String

Application.EnableEvents = False
With ActiveWorkbook
sFile = .Name
If InStr(1, .Name, "(TS ") > 0 Then
sFile = Left(.sFile, InStr(1, "(TS ", .Name) - 1)
End If
If Right(sFile, 4) = ".xls" Then
sFile = Left(sFile, Len(sFile) - 4)
End If
.SaveAs Filename:=sFile & "(TS " & Format(Now, "yyyy mm dd hh mm
ss") & ")"
End With
Cancel = True
Application.EnableEvents = True

End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Back
Top