Return Value of File Date Created

gottimd

Well-known Member
Joined
Jul 29, 2002
Messages
501
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?
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

AgentSmith

Well-known Member
Joined
Mar 8, 2004
Messages
575
Go into VBA and select Tools > References. Search for "Microsoft Scripting Runtime" and tick it. Then enter the following code:
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 (y)
 

AgentSmith

Well-known Member
Joined
Mar 8, 2004
Messages
575
Excuse the syntax errors! :oops:

strPath should be pPath
strFileName should be pFileName
 

gottimd

Well-known Member
Joined
Jul 29, 2002
Messages
501
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

Well-known Member
Joined
Mar 8, 2004
Messages
575
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)?
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,888
Messages
5,766,948
Members
425,389
Latest member
Naresha

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top