old workbook seems to have today's date in E1 on sheet 1

[Max Bialystock]

I have a folder full of workbooks that are named for the day they were
created.

That filename came from E1 =today().

So now if someone prints out an old workbook it seems to have today's date
in E1 on sheet 1.

The filename, though, is correct.



Can I fix this with a vba routine?

 

[Frank Kabel]

Hi
each time you open your workbook the function
=TODAY()
is re-calculated. this is not a timestamp. If you want this you have to
enter the date manually in cell E1

 

[Tom Ogilvy]

To add to Frank's advice,
If you renamed the workbook with code, then you could add a line in the code
that converts the forula to a constant value

Range("E1").formula = Range("E1").Value

Or, if you need the data, you can parse it out of the workbook name if that
is appropriate

Dim sName as String, sDate as String
Dim dtDate as Date
sName = Activeworkbook.Name
sDate = Mid(sName,6,8)
dtDate = cDate(Application.Substitute(sDate,"_","/"))

Which would work for a name like
abcd 04_21_04.xls
as an example.

 

[Max Bialystock]

Tom,

Thanks for that.
The file names are all
abcd defg dd-mm-yy.xls.

Max

 

[Tom Ogilvy]

Demo from the immediate window:

sName1 = "abcd defg 21-04-04.xls"
sName2 = Mid(sName1,14,2) & "/" & _
Mid(sname1,11,2) & "/" & mid(sName1,17,2)
? sName1
abcd defg 21-04-04.xls
? sname2
04/21/04
? cdate(sname2)
4/21/2004
? format(cdate(sname2), "mmm dd, yyyy")
Apr 21, 2004

 

[Max Bialystock]

Tom,

Perfect.
Thank you very much for your help.