Announcement

Collapse
No announcement yet.

Unconfigured Ad Widget

Collapse

Find the modified date of a file?

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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

  • #2
    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.

    Comment


    • #3
      Re: Find the modified date of a file?

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

      Comment


      • #4
        Re: Find the modified date of a file?

        Perfect works a treat thanks very much.

        Comment


        • #5
          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

          Comment


          • #6
            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.
            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

            Comment


            • #7
              Re: Find the modified date of a file?

              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.
              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

              Comment

              Trending

              Collapse

              There are no results that meet this criteria.

              Working...
              X