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)

"Emily" <(E-Mail Removed)> wrote in message
news:6c0e01c3e669$841d1980$(E-Mail Removed)...
> 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!

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

>-----Original Message-----
>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)
>
>"Emily" <(E-Mail Removed)> wrote in
message
>news:6c0e01c3e669$841d1980$(E-Mail Removed)...
>> 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!
>
>
>.
>

On Thu, 29 Jan 2004 13:23:02 -0000, "Bob Phillips"
<(E-Mail Removed)> wrote in
microsoft.public.excel.misc:

>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.
^^^

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.

>"Emily" <(E-Mail Removed)> wrote in message
>news:6c0e01c3e669$841d1980$(E-Mail Removed)...
>> 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!

--
Michael Bednarek http://mbednarek.com/ "POST NO BILLS"

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...#sortallsheets
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Michael Bednarek" <mb at mbednarek dot com> wrote in message news:(E-Mail Removed)...
> On Thu, 29 Jan 2004 13:23:02 -0000, "Bob Phillips"
> <(E-Mail Removed)> wrote in
> microsoft.public.excel.misc:
>
> >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.
> ^^^
>
> 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.
>
> >"Emily" <(E-Mail Removed)> wrote in message
> >news:6c0e01c3e669$841d1980$(E-Mail Removed)...
> >> 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!
>
> --
> Michael Bednarek http://mbednarek.com/ "POST NO BILLS"

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)

"Emily" <(E-Mail Removed)> wrote in message
news:694601c3e66c$76bba010$(E-Mail Removed)...
> 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
>
> >-----Original Message-----
> >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)
> >
> >"Emily" <(E-Mail Removed)> wrote in
> message
> >news:6c0e01c3e669$841d1980$(E-Mail Removed)...
> >> 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!
> >
> >
> >.
> >

Just a quick thanks to you all for your help : }