File Date in Worksheet

[jphensen]

How can I store the Workbook file date in a cell?

[Ivan F Moala]

On 2002-05-15 07:15, jphensen wrote:
How can I store the Workbook file date in a cell?

Hi John
Which File Date ??
Date Created
Date Modified
Date Last accessed

Note: Last accessed & last Modified will be the same if Open.

All these are avail in the Workbooks Properties
Files > Property > General Tab

[Jay Petrulis]

Hi,

Consider the VBA function FileDateTime and the BuiltinDocumentProperties property to return what you want.

See VBA help for details. As Ivan noted, an open workbook has the same properties for a few of the items.

Bye,
Jay

[jphensen]

The date the workbook was modified, not the date accessed.
Thanks,

[WayneTN]

John,

You could use the code below :->

Function LastMod()
'//Finds the File's last modified date/time
'//...display depends on cell date/time format
'//Use: =ROUNDDOWN(LastMod(),0)
'//Returns: mm/dd/yy with minutes & seconds at zero
'//...so exact no. of days since last mod can be calc.

Dim Fso, F
Set Fso = CreateObject("Scripting.FileSystemObject")
Set F = Fso.GetFile("c:yourfilename.xls")
LastMod = F.DateLastModified

End Function

...then put =ROUNDDOWN(LastMod(),0) in the cell you want the last mod date to show up in. (I round down the date to get rid of the minutes & seconds for another calculation that I'm using). Good luck. Wayne

[jphensen]

Looks like Excel 2000 may not be able to do what I wanted. I periodically deliver a Workbook database which reflects data downloaded from another computer (main frame). I include a manual date entry which relfects the date I last modified the workbook, i.e. the data contained herein is current through this date. Since Excel 2000 shows the modified date as the date the workbook is opended the modified date is effectively the same as Now() which does not reflect the date the workbook was last updated (i.e. changed).

[jphensen]

I got it to do what I needed by incorporating the function technique into a larger macro that I run when formatting the data in the workbook. Thanks so much to all and in particular to Wayne.
John Hensen