How to get the date modified for a user defined filename in cell.

[omairhe]

I want to get the date last modified for a user-defined excel filename in cell D2.
D2 =

D:\Users\Michael Phelps\03-2021.xlsb

Answer: 3/5/2021 1:06:34 PM

Is this possible with formula? Thanks.

 

[Anthony47]

Maybe using a "user defined function":
1) copy this code into a standard vba module of your vba project

Function GetFileInfo(ByVal myFile As String, Optional ByVal myPar As Long = 1) As Variant
'see https://www.mrexcel.com/board/threads/how-to-get-the-date-modified-for-a-user-defined-filename-in-cell.1163898/
Dim myFSO As Object
Set myFSO = CreateObject("Scripting.FileSystemObject")
If myPar = 1 Then
    GetFileInfo = myFSO.GetFile(myFile).DateCreated
ElseIf myPar = 2 Then
    GetFileInfo = myFSO.GetFile(myFile).DateLastAccessed
ElseIf myPar = 3 Then
    GetFileInfo = myFSO.GetFile(myFile).DateLastModified
Else
    GetFileInfo = CVErr(2042)
End If
Set fso = Nothing
End Function

2) Then return to your worksheet, you will be allowed to use a formula like this:

=GetFileInfo(FileFullPath&Name;Index)

-FileFullPath&Name is the path + name of the file; could be a string, or a reference to a cell that contains the string, or a formula that create a correct string
-Index can be 1 (the default value)=DateCreated; or 2=DateLastAccessed; or 3=DateLastModified

Examples for valid formulas:

=GetFileInfo(K12,3)                               'DateLastModified
=GetFileInfo(K12)                                       'DateCreated
=GetFileInfo("D:\Dropbox\SHARED\PCF_Workload.xlsm",3)   'string in formula
=GetFileInfo(A2&"\"&B2,3)                               'A2 is the Path, B2 is the Finename

Bye

 

[omairhe]

Maybe using a "user defined function":
1) copy this code into a standard vba module of your vba project

Function GetFileInfo(ByVal myFile As String, Optional ByVal myPar As Long = 1) As Variant
'see https://www.mrexcel.com/board/threads/how-to-get-the-date-modified-for-a-user-defined-filename-in-cell.1163898/
Dim myFSO As Object
Set myFSO = CreateObject("Scripting.FileSystemObject")
If myPar = 1 Then
    GetFileInfo = myFSO.GetFile(myFile).DateCreated
ElseIf myPar = 2 Then
    GetFileInfo = myFSO.GetFile(myFile).DateLastAccessed
ElseIf myPar = 3 Then
    GetFileInfo = myFSO.GetFile(myFile).DateLastModified
Else
    GetFileInfo = CVErr(2042)
End If
Set fso = Nothing
End Function

2) Then return to your worksheet, you will be allowed to use a formula like this:

=GetFileInfo(FileFullPath&Name;Index)

-FileFullPath&Name is the path + name of the file; could be a string, or a reference to a cell that contains the string, or a formula that create a correct string
-Index can be 1 (the default value)=DateCreated; or 2=DateLastAccessed; or 3=DateLastModified

Examples for valid formulas:

=GetFileInfo(K12,3)                               'DateLastModified
=GetFileInfo(K12)                                       'DateCreated
=GetFileInfo("D:\Dropbox\SHARED\PCF_Workload.xlsm",3)   'string in formula
=GetFileInfo(A2&"\"&B2,3)                               'A2 is the Path, B2 is the Finename

Bye

Thank you Anthony.