Date stamp of a file

[gjsala]

Using VBA how would I look at a file on the C: and extract the date stamp?

 

[Nikita6003]

Use the FileSystemObject - check out its properties

Cheers
Nikki
Look, mommy, I'm flying!

 

[N1GHTEYES]

It may depend on exactly which date you are after. Date created, modified, last accessed, last saved...

I had some issues on this a while back and Skip provided some useful info in thread707-1429784.

Tony

 

[fumei]

N1GHTEYES, that only works for a file (like an Office file) that actually has BuiltInDocumentProperties. The OP does not specify this (although I guess you can assume this).

FileSystemObject can get file dates for any file.


unknown

 

[N1GHTEYES]

fumei - I agree, but when I previously raised the issue, it was because the fso was not giving the actual date at which the workbook was last saved - which is what I was after at the time. So, just in case the OP happened to want that info, I mentioned Skip's solution.

 

[fumei]

Fair enough. FSO does return DateLastModified, but admitedly that can be deceiving.


unknown

 

[gjsala]

The files I'm looking at are not excel(or office) files. What would be the difference in code?

 

[N1GHTEYES]

If the files you are after are not office files, then ignore my initial comment.

However, you can use the FileSystemObject - as mentioned by Nikki. This gives you a range of possible dates - see the help on it to check out the options.

To use it you will need to include a reference to the Microsoft Scripting Runtime library. To do so, go to the VBA editor area, select "Tools" and then "References" from the menu, and search for "Microsoft Scripting Runtime". Click the checkbox.

You should then be able to find the filesystemobject in the object viewer and check out its help file.

Here is the code you would use to get the date created value:

Public Function FsysDate(fname As String) As Date
Dim fs, f
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFile(fname)
FsysDate = f.DateCreated
End Function

However, if you are happy with just the Date Created value, then you could just use the following, which does not reference the scripting runtime.

Public Function FDate(fname As String) As Date
FDate = FileDateTime(fname)
End Function

Having done either of the above, you can then call the function you have just created from within a worksheet cell (if you supply it with the full path and filename of the file whose date you want to know).

I hope this helps.

Tony

 

[N1GHTEYES]

The above was written at home. Having got to work, I remembered that a while back I wrote a function which uses the above method to report various file parameters. Here it is:

Public Function ShowFileInfo(filespec As String, infotype As Long) As Variant
'This function uses the scripting runtime library object "FileSystemObject"
'to return details about a specified file.
'
'The available details are:
'1  Attributes
'2  DateCreated
'3  DateLastAccessed
'4  DateLastModified
'5  Drive
'6  name
'7  ParentFolder
'8  Path
'9  ShortName
'10 ShortPath
'11 Size
'12 type

    Dim fs As FileSystemObject
    Dim f As File
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set f = fs.GetFile(filespec)
    Select Case infotype
    Case Is = 1 '    Attributes
        ShowFileInfo = f.Attributes
    Case Is = 2 '    DateCreated
        ShowFileInfo = f.DateCreated
    Case Is = 3 '    DateLastAccessed
        ShowFileInfo = f.DateLastAccessed
    Case Is = 4 '    DateLastModified
        ShowFileInfo = f.DateLastModified
    Case Is = 5 '    Drive
        ShowFileInfo = f.Drive
    Case Is = 6 '    name
        ShowFileInfo = f.Name
    Case Is = 7 '    parentfolder
        ShowFileInfo = f.ParentFolder
    Case Is = 8 '    path
        ShowFileInfo = f.Path
    Case Is = 9 '    shortname
        ShowFileInfo = f.ShortName
    Case Is = 10 '    shortpath
        ShowFileInfo = f.ShortPath
    Case Is = 11 '    size
        ShowFileInfo = f.Size
    Case Is = 12 '    type
        ShowFileInfo = f.Type
    End Select
End Function

You will want to use it with a second argument of 2, 3 or 4 depending on which date you want.

Again, you will need to reference the Scripting Runtime library for the above to work.

Tony