Find the modified date of a file?

[nojy]

Hi,

Has anyone written a piece of vba code that can check the last modified date of a specific file?

THe file will not have a date in its name, so the date needs to be picked up from the last modified value a file has.

Thanks very much,

Jon

[A9192Shark]

nojy,

Have a look at the suggested threads at the top of this page. There are descriptions there of using findfile and lastmodified property.

Good luck,

Alan.

[Richie(UK)]

Hi jon,

You could use FSO, something like this perhaps:

Sub test() 
    MsgBox FileLastModified("C:\My Documents\abook.xls") 
End Sub 
 
Function FileLastModified(strFullFileName As String) 
    Dim fs As Object, f As Object, s As String 
     
    Set fs = CreateObject("Scripting.FileSystemObject") 
    Set f = fs.GetFile(strFullFileName) 
     
    s = UCase(strFullFileName) & vbCrLf 
    s = s & "Last Modified: " & f.DateLastModified 
    FileLastModified = s 
     
    Set fs = Nothing: Set f = Nothing 
     
End Function 

HTH

[nojy]

Perfect works a treat thanks very much.

[nojy]

Hi,

I am using the code above to find the last modiefied date of file, but I am also trying to find a away that if the file was not found it would flag up an error message and do something else...etc

Any ideas?

Thanks

Jon

[Richie(UK)]

Hi Jon,

Just use a function to test for the file first. The one below uses Dir but you could use the FSO FileExists as an alternative.

Sub test() 
    Const strFullName As String = "C:\My Documents\nosuchbook.xls" 
     
    If FileExists(strFullName) Then 
        MsgBox FileLastModified(strFullName) 
    Else 
        MsgBox "Cannot find the file : " & vbNewLine & strFullName 
    End If 
     
End Sub 
 
Private Function FileExists(fname) As Boolean 
    Dim x As String 
    x = Dir(fname) 
    If x <> "" Then FileExists = True _ 
Else FileExists = False 
End Function 
 
Function FileLastModified(strFullFileName As String) 
    Dim fs As Object, f As Object, s As String 
     
    Set fs = CreateObject("Scripting.FileSystemObject") 
    Set f = fs.GetFile(strFullFileName) 
     
    s = UCase(strFullFileName) & vbCrLf 
    s = s & "Last Modified: " & f.DateLastModified 
    FileLastModified = s 
     
    Set fs = Nothing: Set f = Nothing 
     
End Function 

HTH

[ramkisuni]

Hi Jon,

Just use a function to test for the file first. The one below uses Dir but you could use the FSO FileExists as an alternative.

Sub test() 
    Const strFullName As String = "C:\My Documents\nosuchbook.xls" 
     
    If FileExists(strFullName) Then 
        MsgBox FileLastModified(strFullName) 
    Else 
        MsgBox "Cannot find the file : " & vbNewLine & strFullName 
    End If 
     
End Sub 
 
Private Function FileExists(fname) As Boolean 
    Dim x As String 
    x = Dir(fname) 
    If x <> "" Then FileExists = True _ 
Else FileExists = False 
End Function 
 
Function FileLastModified(strFullFileName As String) 
    Dim fs As Object, f As Object, s As String 
     
    Set fs = CreateObject("Scripting.FileSystemObject") 
    Set f = fs.GetFile(strFullFileName) 
     
    s = UCase(strFullFileName) & vbCrLf 
    s = s & "Last Modified: " & f.DateLastModified 
    FileLastModified = s 
     
    Set fs = Nothing: Set f = Nothing 
     
End Function 

HTH

Hi,
I am able to execute this code and it works. But it prints a wrong date. My setup is like

The file is in SharePoint server
My installed Excel version 2010
The file is with .xls extn (excel 2003 version)
my VBA version is 7.0

The problem is when I open this file , in the File tab it shows correct Last Modified Date but when I get it through VBA it shows some old date;any help in this regard is really appreciated since I am not getting why this issue.

Thanks