Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Mar 2009
    Location
    Indiana
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Can file properties be displayed in a cell on a spreadsheet?
    Specifically, on the Workbook Properties window, under the Statistics tab, how can you display the Modified: and Last saved by: fields in a cell on the spreadsheet?

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts
    [quote name='COLPete' post='775373' date='15-May-2009 09:39']Can file properties be displayed in a cell on a spreadsheet?
    Specifically, on the Workbook Properties window, under the Statistics tab, how can you display the Modified: and Last saved by: fields in a cell on the spreadsheet?[/quote]
    Not sure there's a formula way, but you can use these VBA UDFs:

    Function LastModDate() As Date
    LastModDate = ActiveWorkbook.BuiltinDocumentProperties("Last Save Time")
    End Function

    Function CreateDate() As Date
    CreateDate = ActiveWorkbook.BuiltinDocumentProperties("Creation Date")
    End Function

    In the spreadsheet cells, enter "=LastModDate()" and "=CreateDate()" without the quote marks, format the cells as Date.
    -John ... I float in liquid gardens
    UTC -7±DS

  3. #3
    New Lounger
    Join Date
    Mar 2009
    Location
    Indiana
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='JohnBF' post='775390' date='15-May-2009 13:19']Not sure there's a formula way, but you can use these VBA UDFs:

    Function LastModDate() As Date
    LastModDate = ActiveWorkbook.BuiltinDocumentProperties("Last Save Time")
    End Function

    Function CreateDate() As Date
    CreateDate = ActiveWorkbook.BuiltinDocumentProperties("Creation Date")
    End Function

    In the spreadsheet cells, enter "=LastModDate()" and "=CreateDate()" without the quote marks, format the cells as Date.[/quote]

    JohnBF, thanks for the reply.
    I've never used VBA scripts, but I get what you’re talking about. I did exactly what you suggested, however it didn't give me the answer I'd expected. When I go to "File", "Properties", "Statistics" tab, on the Modified line it states Monday, May 18, 2009 1:17:38 PM.
    This is the date I'm trying to get to display within a cell. When I used your VB script and entered "=LastModDate()" I got in response Saturday, January 00, 1900 and I got a $VALUE! error for "=CreateDate()"

    Both cells were formatted as date cells with a type selected as *Wednesday, March 14, 2001 (this is the option I selected in the type field).

    I'm guessing that if I can get the information on the last modified date using the VB script you wrote, I should also be able to get the name of who last modified the file. I tried this as an addition to what you suggested.

    Function CreateName() As General
    CreateName = ActiveWorkbook.BuiltinDocumentProperties("Last saved by")

    End Function

    I formatted the cell as general.

    Again, I got an error for this attempt at VB script so I'm guessing I'm doing something wrong.

    I'm willing to learn VB. What resource = book would you recommend I get to get me started?

    Thanks again for taking the time to reply to my original query.

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts
    [quote name='COLPete' post='775738' date='18-May-2009 11:37']I've never used VBA scripts, but I get what you’re talking about. I did exactly what you suggested, however it didn't give me the answer I'd expected. When I go to "File", "Properties", "Statistics" tab, on the Modified line it states Monday, May 18, 2009 1:17:38 PM.
    This is the date I'm trying to get to display within a cell. When I used your VB script and entered "=LastModDate()" I got in response Saturday, January 00, 1900 and I got a $VALUE! error for "=CreateDate()"

    Function CreateName() As General
    CreateName = ActiveWorkbook.BuiltinDocumentProperties("Last saved by")
    End Function

    Again, I got an error for this attempt at VB script so I'm guessing I'm doing something wrong.

    I'm willing to learn VB. What resource = book would you recommend I get to get me started?[/quote]
    Without your work, I can't see what the problem might be, so attached is a file that should help. The Creator Name is "Author", as follows:

    Function Creator() As String
    Creator = ActiveWorkbook.BuiltinDocumentProperties("Author")
    End Function

    Function LastModBy() As String
    LastModBy = ActiveWorkbook.BuiltinDocumentProperties("Last Author")
    End Function

    I believe these fields don't become valid until the file is saved once.

    By the way, welcome to the Lounge. We hope you find it helpful and fun.
    Attached Files Attached Files
    -John ... I float in liquid gardens
    UTC -7±DS

  5. #5
    New Lounger
    Join Date
    Mar 2009
    Location
    Indiana
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='JohnBF' post='775758' date='18-May-2009 14:56']Without your work, I can't see what the problem might be, so attached is a file that should help. The Creator Name is "Author", as follows:

    Function Creator() As String
    Creator = ActiveWorkbook.BuiltinDocumentProperties("Author")
    End Function

    Function LastModBy() As String
    LastModBy = ActiveWorkbook.BuiltinDocumentProperties("Last Author")
    End Function

    I believe these fields don't become valid until the file is saved once.

    By the way, welcome to the Lounge. We hope you find it helpful and fun.[/quote]
    Thanks again for the prompt reply.

    It looks like this got me there 1/2 way. I'm getting the same response for both Creator() and LastModBy(). I had someone else modify the workbook so the "Last saved by" field in the properties tab was different than the Author, and still the author's name was displayed for both the Cretor() and the LasModBy() in the cell set as =Creator() and = LastModBy()

    How did you determine the name of the Author field to be "Author"?

    If I knew that I could then find out what the name of the "Last saved by:" (in the properties "Statistics" tab) field is called and that should do the trick.

    By the way, the forum is a wealth of information. I've done some searching and you guys really rock when it comes to helping guys figure this stuff out. Thanks for all you do, it seems a lot of people get a lot more done with the assistance provided by this forum.

  6. #6
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts
    [quote name='COLPete' post='775770' date='18-May-2009 12:49']It looks like this got me there 1/2 way. I'm getting the same response for both Creator() and LastModBy(). I had someone else modify the workbook so the "Last saved by" field in the properties tab was different than the Author, and still the author's name was displayed for both the Cretor() and the LasModBy() in the cell set as =Creator() and = LastModBy()

    How did you determine the name of the Author field to be "Author"? If I knew that I could then find out what the name of the "Last saved by:" (in the properties "Statistics" tab) field is called and that should do the trick.[/quote]
    I just tested again, and I'm not having your problem, but try this on all four functions; add a first line that says "Application.Volatile", as in:

    Function LastModBy() As String
    Application.Volatile
    LastModBy = ActiveWorkbook.BuiltinDocumentProperties("Last Author")
    End Function

    In the Excel 2003 Visual Basic Editor I determined the Author and Last Author Fieldnames by clicking the word ".BuiltinDocumentProperties" and hitting the F1 key which pops teh Help for that Property, than clicking on DocumentProperties, then DocumentProperty, this gave me the names of the built-in document properties.
    -John ... I float in liquid gardens
    UTC -7±DS

Posting Permissions

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