Finding the created date

[derekbez]

Greetings all.

Is it possible to programmatically find the Created Date and/or Last Saved Date from a batch of spreadsheets? I'm guessing it would take a VB or other script???

The issue is this: We have a ton of spreadsheets in a multitude of folders and subfolders. We need to know the relative age of each file, so as to determine the validity and versions. They are primarily stored in Sharepoint, and some/most have been moved, so the date displayed is the date the file was last touched (moved), but not necessarily opened or saved from Excel. In other words, the operating system date is useless to us.

So to summarise, I'm looking for a script of some sort that will iterate over all my Excel files and return the filename+Excel internal date.

Any clues, suggestions, pointers, links etc appreciated!

Derek

 

[jimrward]

here is a selection of functions I use to retrieve stuff, note most of the dates will come back in UK/British format, but you should be able to change the order in the functions, also included some to get Author as well

Function GetCreateYY(filespec)
' Stop
Dim fs, f, s
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFile(filespec)
s = f.DateCreated
GetCreateYY = Mid$(s, InStr(1, s, " ") - 2, 2)
Set fs = Nothing
Set f = Nothing
Set s = Nothing
End Function

Function GetCreated(filespec)
Dim fs, f
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFile(filespec)
GetCreated = Format(f.DateCreated, "dd mmm yyyy")
Set fs = Nothing
Set f = Nothing
End Function

Function GetDDMMYYCreated(filespec)
' Stop
Dim fs, f, s
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFile(filespec)
s = f.DateCreated
dd = Left$(s, 2)
mm = Mid$(s, 4, 2)
YY = Mid$(s, InStr(1, s, " ") - 2, 2)
GetDDMMYYCreated = dd & mm & YY
Set fs = Nothing
Set f = Nothing
Set s = Nothing
Set dd = Nothing
Set mm = Nothing
Set YY = Nothing
End Function

Function GetDDMMYYModified(filespec)
' Stop
Dim fs, f, s
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFile(filespec)

s = f.Datelastmodified
dd = Left$(s, 2)
mm = Mid$(s, 4, 2)
YY = Mid$(s, InStr(1, s, " ") - 2, 2)
GetDDMMYYModified = dd & mm & YY
Set fs = Nothing
Set f = Nothing
Set s = Nothing
Set dd = Nothing
Set mm = Nothing
Set YY = Nothing
End Function

Function GetAuthor(FileName)
Dim DSO As DSOFile.OleDocumentProperties
Set DSO = New DSOFile.OleDocumentProperties
GetAuthor = "unknown"
On Error Resume Next
DSO.Open sfilename:=FileName
GetAuthor = DSO.SummaryProperties.Author
DSO.Close
End Function

Function GetAuthor2(FileName As String) As String
'
'code will need a reference to the "DS: OLE Document Properties 1.2 Object Library" .
'Go to the Tools menu, choose References, and select this library.
'If you do not have this library installed, you can download it for free from Microsoft at
'http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q224351 .
'
Dim DSO As DSOleFile.PropertyReader
Set DSO = New DSOleFile.PropertyReader
GetAuthor2 = DSO.GetDocumentProperties(sfilename:=FileName).Author
Set DSO = Nothing
End Function