Results 1 to 4 of 4

Thread: Want help in using DateLastModified in VBA of Excel

  1. #1
    Join Date
    Oct 2011
    Posts
    97

    Want help in using DateLastModified in VBA of Excel

    How can I use the DateLastModified property in VBA to pull into my current open spreadsheet, the last modified date of another file is one that is closed. I want, if possible, to add it to a cell. As always, your help is greatly appreciated.

  2. #2
    Join Date
    Jul 2011
    Posts
    441

    Re: Want help in using DateLastModified in VBA of Excel

    There is a small code which I am providing you below. Look at the same and then check it. Gives the date/time last saved to it:
    Code:
    Function lastsaved() As Double
    lastsaved = ActiveWorkbook.BuiltinDocumentProperties(12)
    End Function

  3. #3
    Join Date
    Jul 2011
    Posts
    435

    Re: Want help in using DateLastModified in VBA of Excel

    Run the first macro to get second macro to return the date.
    Code:
    Sub anotherfiledate()
    ShowFileAccessInfo ("closedfile.xls")
    End Sub
    
    Sub ShowFileAccessInfo(filespec)
    Dim fs, f, s
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set f = fs.GetFile(filespec)
    s = UCase(filespec) ' & vbCrLf
    Range("K3").Value = s & " Last Modified: " & f.DateLastModified
    End Sub

  4. #4
    Join Date
    May 2012
    Posts
    1

    Re: Want help in using DateLastModified in VBA of Excel

    I have never tried entering VBA code before, I have always used Excel's inherent formulas to complete needed tasks, so I may need some further instruction if you wouldn't mind. I too need to list the modified date of another file that is located on a seperate drive. I pasted your code into module 1:

    Sub anotherfiledate()
    ShowFileAccessInfo ("Interventional.xlsx")
    End Sub

    Sub ShowFileAccessInfo(filespec)
    Dim fs, f, s
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set f = fs.GetFile(filespec)
    s = UCase(filespec) ' & vbCrLf
    Range("K3").Value = s & " Last Modified: " & f.DateLastModified
    End Sub


    and changed the "closedfile.xls" to the document I am looking for. Do I need to list the whole path name ("H:\EXAMPLE FOLDER\Interventional.xlsx")?
    Also, how do I get the code to display the other documents modified date in a specific cell (B1) within my active workbook? Thank you so much!
    -T

Similar Threads

  1. Replies: 6
    Last Post: 17-02-2012, 12:18 PM
  2. Cell format errors appears in Excel 2003 but not in Excel 2000
    By @nki$achi in forum MS Office Support
    Replies: 1
    Last Post: 24-01-2012, 04:00 PM
  3. Excel 2007 file fails to get permission to open in Excel 2011
    By Raju Chacha in forum Windows Software
    Replies: 6
    Last Post: 13-01-2012, 08:17 PM
  4. Replies: 6
    Last Post: 17-05-2011, 10:00 PM
  5. Replies: 4
    Last Post: 13-02-2011, 09:37 AM

Posting Permissions

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