Return Value of File Date Created

[gottimd]

If I have in Cell A1 the root directory (i.e. C:\My documents\March) and b1 has the file name (Worksheet1.xls), is there a way to put in b2, for the formula to look into the root directory, search for the file name, and return when the date the file was created? And in b3, when it was modified if at all? And if the file is not available, to return the text value of "File Not Available"?

Please note that the month will change often. Is this possible?

 

[AgentSmith]

Go into VBA and select Tools > References. Search for "Microsoft Scripting Runtime" and tick it. Then enter the following code:

Function GetDateCreated(ByVal pPath As String, ByVal pFileName As String) As Variant
    Dim strFullName As String
    Dim objFSO As FileSystemObject
    
    'Sort out the full file name
    strFullName = strPath
    If Right(strFullName, 1) <> "\" Then strFullName = strFullName & "\"
    strFullName = strFullName & strFileName
    
    'If the file does not exist then return n/a
    If Dir(strFullName) = "" Then
    
        GetDateCreated = "File Not Available"
        
    'Otherwise return the date it was created
    Else
    
        Set objFSO = New FileSystemObject
        GetDateCreated = objFSO.GetFile(strFullName).DateCreated
        Set objFSO = Nothing
    
    End If
End Function

Copy this to create another function called "DateLastModified" and change ".DateCreated" to ".DateLastModified". Then

 

[AgentSmith]

Excuse the syntax errors!

strPath should be pPath
strFileName should be pFileName

 

[gottimd]

Will this work if I have in the following:

a1= Drop Down of the Root Directory (i.e. C:\My Documents\)
This won't change

a2=Month (i.e January 2004 Consolidation\)
obviously this will change, which I want to make a drop down to change

a3=sub Root Directory (i.e. Entity Packs\)
This won't change

a4=Corporation # (i.e. [111.xls])
Three digit number will change,, which I want to make a drop down to change


I will have a list of this Corporations going down column B, and I wanted to be able to just change the month, and it will give the date the file was created in the next column over like:

001 12/23/2004
002 12/22/2004
003 12/23/2004
004 12/21/2004



Does the above code work like this?

 

[AgentSmith]

I'm not sure I follow you entirely, but I *think* the answer is "yes"!

Give it a go and see what happens. If it doesn't work then could you post an excerpt using Colo's HTML Maker (link at bottom of page)?