Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 7 of 7

Thread: Find the modified date of a file?

  1. #1
    Join Date
    19th October 2004
    Posts
    57

    Find the modified date of a file?

    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

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    14th September 2004
    Location
    Surrey, England
    Posts
    802

    Re: Find the modified date of a file?

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    25th January 2003
    Location
    UK
    Posts
    2,745

    Re: Find the modified date of a file?

    Hi jon,

    You could use FSO, something like this perhaps:
    VB:
    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
    Cross-poster? Read this: Cross-posters
    Struggling to use tags (including Code tags)? : Forum tags

  4. #4
    Join Date
    19th October 2004
    Posts
    57

    Re: Find the modified date of a file?

    Perfect works a treat thanks very much.

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    19th October 2004
    Posts
    57

    Re: Find the modified date of a file?

    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

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    25th January 2003
    Location
    UK
    Posts
    2,745

    Re: Find the modified date of a file?

    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.
    VB:
    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
    Cross-poster? Read this: Cross-posters
    Struggling to use tags (including Code tags)? : Forum tags

  7. #7
    Join Date
    31st December 2011
    Posts
    1

    Re: Find the modified date of a file?

    Quote Originally Posted by Richie(UK) View Post
    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.
    VB:
    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

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 3 users browsing this thread. (0 members and 3 guests)

Possible Answers

  1. Open Latest File By Date Modified
    By KLL in forum EXCEL HELP
    Replies: 9
    Last Post: March 1st, 2008, 02:08
  2. Date Last Modified From CSV File
    By cluffy in forum EXCEL HELP
    Replies: 2
    Last Post: August 11th, 2007, 13:49
  3. Date Modified Of Text .txt File
    By georgebrough in forum EXCEL HELP
    Replies: 2
    Last Post: January 8th, 2007, 19:12
  4. Obtain imported file modified date
    By mikeburg in forum EXCEL HELP
    Replies: 4
    Last Post: January 22nd, 2006, 15:02
  5. FileSearch based on modified date of file
    By TroyArthur in forum EXCEL HELP
    Replies: 1
    Last Post: January 6th, 2006, 00:02

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno