Read external file properties (date created) using VBA

[kiabosh]

Hi All,

Can anybody tell me how to read the properties of a saved file using VBA.

Specifically I want to read the date created property of an external file before continuing to import the data from that file into my current workbook.

This will form part of an error trapping procedure - ie not continue with the import if the external file has not been updated within a given period.

This is probably very simple but I've searched the forum for the last hour without any success.

Please be gentle with me, I'm very much a novice with VBA.

Cheers

 

[dk]

Here's one way:

Sub GetDateCreated()

    Dim oFS As Object
    Dim strFilename As String

    'Put your filename here
    strFilename = "c:\excel stuff\commandbar info.xls"


    'This creates an instance of the MS Scripting Runtime FileSystemObject class
    Set oFS = CreateObject("Scripting.FileSystemObject")

    MsgBox strFilename & " was created on " & oFS.GetFile(strFilename).DateCreated



    Set oFS = Nothing

End Sub

 

[kiabosh]

Excellent!!

Worked first time.

Thanks very much

 

[uklobster]

This is almost exactly what Im looking for and it works a treat, is there any way to chnge this so that it displays date modified rather than created ?

The CSV Im using is overwritten repeatedly and I need to know the date of the last overwrite, not when the initial file was first created.

Cheers, would appreciate any help as this is driving me nuts - our guys who use this spreadsheet keep forgetting to run a new download from our SOP system - and dont realise they are working with old data.

TIA

 

[uklobster]

VBA help seems to hint at using LastUpdated

But knowing nothing about VBA I dont know how to get this to work

Please help me !!

 

[dk]

Hi,

You can just use the DateLastModified property rather than DateCreated e.g.

Sub GetDateLastModified()

    Dim oFS As Object
    Dim strFilename As String

    'Put your filename here
    strFilename = "c:\temp\find files.xls"


    'This creates an instance of the MS Scripting Runtime FileSystemObject class
    Set oFS = CreateObject("Scripting.FileSystemObject")

    MsgBox strFilename & " was last modified on " & oFS.GetFile(strFilename).Datelastmodified



    Set oFS = Nothing

End Sub

 

[uklobster]

ROFL I just spent about two days trying to get this sorted and the second I suss it out, you reply with exactly the same answer I came to - well someone else came to and I copied from Though I guess you can measure your response time in seconds rather than days !!

Many thanks really appreciated !!

 

[dk]

No worries mate, any time

 

[ddubnansky]

I'm looking for something similar for Win7 in Excel2010. However I need the "DateTaken" date. I'm trying to create a spreadsheet that will let me rename my photos using the date I took the picture (since Creation and Last Modified will not work for me). Can you help me with this?

Since this is an available field under Windows Explorer (in Win7), so I don't understand why I'm having so much difficulty figuring this out.

Thanks.

 

[AshJT]

Hi

I'm also looking at something similar to this - I can read metadata from a file by using fso code, but is there a way to change this data?

Ideally I want to change the Author data field. Any ideas?

Cheers