Returning the last modified date to a cell in excel 2003

[Guest]

I want to return the File Property value of the excel file's last modified
date to a cell in a worksheet. Is this possible? I seem to recall that
there may be some way of using EXCEL4 macro, but cannot remember how. Or
perhaps there is an easier way.

Many thanks

 

[Guest]

It's the FileDateTime VBA function you are looking for. You can create a
simple UDF to reach time stamp on a worksheet.

Regards,
Stefi



„Dunc†ezt írta:

 

[David L.]

I found the following code on Microsoft.com, which is what I think I need.

1. But how would I then reference this information in a cell?
2. Also, how would I make this code generic so the filename would be
whatever the active file is?

Thanks!

Dim MyStamp
' Assume TESTFILE was last modified on February 12, 1993
' at 4:35:47 PM.
' Assume English/U.S. locale settings.
MyStamp = FileDateTime("TESTFILE")
' Returns "2/12/93 4:35:47 PM".

 

[Stefi]

Install this UDF in a normal module:
Function MyStamp()
Application.Volatile
MyStamp = FileDateTime(ThisWorkbook.FullName)
End Function

and use it in a cell as an UDF:
= MyStamp()

Works only after the active workbook has been saved as an .XLS file.

Regards,
Stefi


„David L.†ezt írta:

 

[MikeC@Trane]

Hey Stefi--very cool! Thanks.
--
MikeC@Trane

Install this UDF in a normal module:
Function MyStamp()
Application.Volatile
MyStamp = FileDateTime(ThisWorkbook.FullName)
End Function

and use it in a cell as an UDF:
= MyStamp()

Works only after the active workbook has been saved as an .XLS file.

Regards,
Stefi


„David L.†ezt írta:

 

[Rob]

How can I add this to a page footer?

Hey Stefi--very cool! Thanks.

 

[PSU35]

Stefi
I came across yor response below and tried it just as shown. Unfortunately
it returns #NAME?.
Do I need to add something?
I assumed it would return the date without specifing the file name.
Rick

 

[Rick Rothstein]

Did you save the function where Stefi said to put it, namely, a Module? In
the VB editor, click on Insert/Module in its menu bar and copy paste the
code in the code window that opens up. Now go back to the worksheet and hit
F9 to force an update.