Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    St. Charles, Illinois
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts

    time stamp (Excel 2000)

    I have one spreadsheet that grabs/links data from 164 other files and displays the data in cells C5:C169. I would like the next cell over (Column D) to time stamp when each of the 164 files were last saved. Is this possible? Thank you.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: time stamp (Excel 2000)

    You can create a custom function in a standard module:

    Function DateLastModified(FileName As String) As Date
    Dim fso As Object
    On Error Resume Next
    Set fso = CreateObject("Scripting.FileSystemObject")
    DateLastModified = fso.GetFile(FileName).DateLastModified
    Set fso = Nothing
    End Function

    In the cells in column D, enter a formula like this, with the appropriate file path/name substituted:

    =DateLastModified("C:ExcelSomeFile.xls")

  3. #3
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    St. Charles, Illinois
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: time stamp (Excel 2000)

    Thank you. This is what happened -
    Formula:
    =DateLastModified('M:2004 Cascading Financials2005 PlanBSDAJG RMS[3-yr plan BSD-AJGRMS-RMS.xls")

    Answer:
    1/0/00 12:00 AM
    What did I do to get the wrong answer. The right answer should have been 8/25/04 3:20 PM

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: time stamp (Excel 2000)

    A possible modification of Hans's custom function :<pre> Function DateLastModified(c As Range) As Date
    Dim fso As Object, strFile As String
    strFile = Replace(Mid(c.Formula, 3, InStr(c.Formula, "]") - 3), "[", "")
    On Error Resume Next
    Set fso = CreateObject("Scripting.FileSystemObject")
    DateLastModified = fso.GetFile(strFile).DateLastModified
    Set fso = Nothing
    End Function</pre>

    This function should extract the path from the formula in a given cell. In D5 enter

    = DateLastModified(C5)

    and copy down

    Andrew C

  5. #5
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: time stamp (Excel 2000)

    Try (with Hans's original formula)

    =DateLastModified("M:2004 Cascading Financials2005 PlanBSDAJG RMS3-yr plan BSD-AJGRMS-RMS.xls")

    Andrew C

  6. #6
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    St. Charles, Illinois
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: time stamp (Excel 2000)

    I copied over Han's function text with yours and I also changed the formula as you stated. Now I get the #Value! Please help.

  7. #7
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: time stamp (Excel 2000)

    There might be some confusion so try Han's original formula (ignore the changes I suggested) as follows :

    =DateLastModified("M:2004 Cascading Financials2005 PlanBSDAJG RMS3-yr plan BSD-AJGRMS-RMS.xls")

    or if C5 contains formula with a link to another file try the amended function I suggested as follows :

    = DateLastModified(C5)

    If you still experience problems , try posting an example file with fictitious data, links etc .

    Andrew C

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: time stamp (Excel 2000)

    The function (both my version and Andrew's version) use the scripting library. If you are on a corporate network, it is possible that the network admins have disabled scripting.

    I have attached a version that uses only Windows API functions. It is much longer, but should work on any Windows system. You can copy the code into your module. Use it like this:

    =DLM("M:2004 Cascading Financials2005 PlanBSDAJG RMS3-yr plan BSD-AJGRMS-RMS.xls")
    or
    =DateLastModified(C5)
    where C5 is a cell containing a link to an external workbook.

Posting Permissions

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