excel/word file creation date (how to access remotely?) 1

[TomYC]

I am working with excel files, linked. in one cell of one of the files, I would like to display the original file creation date of a different file (possibly in a different location, i.e. with path info). The reason is that I am using my first file as a dashboard (e.g., dashboard.xls), and the other files (e.g. data2.xls, etc.) come to me periodically from some HR source, and perhaps mainframe. If they have unhelpful or varying names, I'd like to save them over into my \data folder to cell reference, but still be able to identify the original date of this data file on my dashboard.xls screen.
I think I want something such as ActiveWorkbook.BuiltinDocumentProperties("Creation Date")
-- can I use VBA to have this date display when a file is opened or updated, etc.?

 

[macropod]

You don't need to open a file to get it's creation date. Instead, you can use code like:

Sub Demo()
' get the file's Creation date.
Dim FSO As Object
Dim oItem As Object
Set FSO = CreateObject("Scripting.FileSystemObject")
Set oItem = FSO.GetFile(ActiveDocument.FullName)
MsgBox oItem.DateCreated
Set oItem = Nothing: Set FSO = Nothing
End Sub

Cheers
Paul Edstein
[MS MVP - Word]

 

[macropod]

Oops!
Change:
ActiveDocument.FullName
to:
"Full Path & Filename"
(ie insert the full path & name of the file to be tested, as a string).

Cheers
Paul Edstein
[MS MVP - Word]

 

[SkipVought]

Hi,

You could code a FUNCTION like this to use on your sheet like any other function, or in VBA...

Function FileCreateDate(sName As String)
    ' get the file's Creation date.
    FileCreateDate = CreateObject("Scripting.FileSystemObject").GetFile(sName).DateCreated
End Function

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]