Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    GBeaucaire
    Guest

    Document info in a cell (Excel 2000)

    This should be easy for you pros... something I can't find in the documentation anywhere.

    I need the following information to appear separate cells on a worksheet, and I don't want to use the header/footer.

    DATE FILE FIRST CREATED - never changing date.
    DATE FILE LAST SAVED - updated each time file saved/printed.
    FILENAME - not the full path, just the filename.

    I use the following formula for the 3rd request:

    <pre>="("&MID(CELL("filename"), FIND("[", CELL("filename"))+1, FIND("]", CELL("filename"))- FIND("[", CELL("filename")) -1)&")"</pre>


    The only problem with that formula is that if you have a bunch of files open with the same formula, they ALL show the filename of the last saved document. If I have three files open, save one, then scroll through the three, they all list the name of the last saved doc in the cells that have this formula. Now, if I try to PRINT one of the two that are showing the incorrect FILENAME, it somehow updates and prints properly, but it's confusing when working in general.

    The main reason the last one is needed is because I use this particular template in full-screen mode, so I lose the title bar which would normally display this info.

    Thanks in advance for your help.

    Jerry Beaucaire

  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

    Re: Document info in a cell (Excel 2000)

    Are you comfortable with VBA code? The attached uses VBA code with Workbook Open and Workbook Close events to stamp these attributes into appropriate named ranges in Column C, rather than formulas. To get to the code, open the VBE using Alt-F11, and in the left pane double click the "ThisWorkbook" object that appears below the sheet object as a child of the VBA project with the attachment filename.

    The code you need may vary according to how you want this to work, but I'm assuming you are intending this for a template which is opened by users, not an empty workbook. This code, for example, always saves the WB with it's present name after the user closes, whether the user wants to save or not.
    Attached Files Attached Files
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    GBeaucaire
    Guest

    Re: Document info in a cell (Excel 2000)

    Thanks for the help. The CREATE DATE and the LAST SAVED DATES need to imbed themselves into a cell while the sheet is open and being worked on. Is there a function that would accomplish that? These files are created and printed before closing.

    Jerry

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Document info in a cell (Excel 2000)

    Hi,
    The following function should do what you want I think:
    Function GetWbkInfo(varType)
    Application.Volatile
    Select Case varType
    Case "Name"
    GetWbkInfo = Application.Caller.Parent.Parent.Name
    Case Else
    GetWbkInfo = Application.Caller.Parent.Parent.BuiltinDocumentPr operties(varType)
    End Select
    End Function
    To get creation date, you would use
    =getwbkinfo("Creation date") or =getwbkinfo(11)
    to get last saved time you would use
    =getwbkinfo("Last Save Time") or =getwbkinfo(12)
    and to get the file name you would use
    =getwbkinfo("Name")
    You can also use it to get any other BuiltInDocumentProperties - just look up the names/index number in help.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    GBeaucaire
    Guest

    Re: Document info in a cell (Excel 2000)

    Rory,

    I placed the =getwbkinfo(11) into a cell and got the expected =NAME? error.

    I placed the Function code you suggested into my EventClass module and also ThisWorkbook.xls and neither had any effect. Where do i put that code so that the cells will use the new function?

    Thanks in advance for the help... I thought it would be something along these lines with the BuiltinDocProps, just couldn't figure out how to make these calls.

    Jerry

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Document info in a cell (Excel 2000)

    Jerry,
    It needs to go into a standard module (not a class module) either in the workbook you want to use it in, or in a Personal Macro Workbook in which case you'll need to use something like
    ='Personal.xls'!getwbkinfo(11)
    depending on the name of your macro workbook.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    GBeaucaire
    Guest

    Re: Document info in a cell (Excel 2000)

    I did everything the way you indicated and couldn't get the cells to update and show the dates...so I went to bed.

    In the morning, when i reloaded the template, the dates were there... go figure. So this is a function that works during the document opening?

    So the fiunction now appears to be active, but I'm not getting what I actually need. Here's what's going on:

    In the cell marked "Create Date" I get the date the TEMPLATE was created... two years ago! <img src=/S/smile.gif border=0 alt=smile width=15 height=15> When I check the properties of the file, this date is in the "created" line on the STATISTICS tab. The date I need is the "Created" field on the GENERAL tab, the date THIS form was created from the template.

    Interestingly, the MODIFIED fields on both the GENERAL and STATISTICS tab (Properties dialog) appear to show the same date, so this doesn't create a problem for the second date I need, the LAST MODIFIED.

    Any idea how to get the correct date?

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

    Re: Document info in a cell (Excel 2000)

    John,<pre>ActiveWorkbook.BuiltinDocumentProperties ("Last save time")</pre>

    will return the time and date of the last save on my Excel 2000. You may need to apply the correct date/time formatting to the cell that takes the value.

    Andrew C

  9. #9
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Document info in a cell (Excel 2000)

    Bother!

    Sub FileLastModStamp()
    MsgBox FileDateTime(ThisWorkbook.FullName)
    Range("B2").Value = FileDateTime(ThisWorkbook.FullName)
    Range("B3").Value = ActiveWorkbook.BuiltinDocumentProperties("Last save time")
    End Sub

    Third line errors out in Excel 97 with run-time error 1004, 'Application-defined or Object defined error'. Is this an ID Ten-T class error on my part?
    -John ... I float in liquid gardens
    UTC -7ąDS

  10. #10
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Document info in a cell (Excel 2000)

    Rory has provided you functions as you requested (though I have an idea about the Last Modified one), but considering that you are working with a template which I assume is distributed AND the underlying create date for that template was two years ago, but the create date you want is when the user changes the template, I'd still be inclined to go with WorkBook Events. The attached version stamps all three values when the WB is opened, printed, saved or closed.

    [Attachment changed to use ThisWorkbook.Fullname instead of ThisWorkbook.Name]
    Attached Files Attached Files
    -John ... I float in liquid gardens
    UTC -7ąDS

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

    Re: Document info in a cell (Excel 2000)

    John,

    I tried that with XL97 and had no errors - correct information returned. Try <pre> ? ActiveWorkbook.BuiltinDocumentProperties("Last save time")</pre>

    in the immediate window and see what is returned.

    Andrew

  12. #12
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Document info in a cell (Excel 2000)

    I get an Run-time error -2147467259(80004005), Automation error. I reviewed all my library references, no obvious ones missing. <img src=/S/hmmn.gif border=0 alt=hmmn width=15 height=15>
    -John ... I float in liquid gardens
    UTC -7ąDS

  13. #13
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Document info in a cell (Excel 2000)

    Rory, seems like Excel97 WB's don't report Last Save Time:

    Sub whatprop()
    Dim rw As Integer
    Dim prop As Variant
    rw = 1
    Worksheets(1).Activate
    For Each prop In ActiveWorkbook.BuiltinDocumentProperties
    Cells(rw, 1).Value = prop.Name
    On Error Resume Next
    Cells(rw, 2).Value = ActiveWorkbook.BuiltinDocumentProperties(rw)
    rw = rw + 1
    Next prop
    End Sub

    Am I missing something?
    -John ... I float in liquid gardens
    UTC -7ąDS

  14. #14
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Document info in a cell (Excel 2000)

    Hi,
    Try this function instead:
    <pre>Function ShowFileAccessInfo(strReturn As String)
    Dim fsoTemp As FileSystemObject, filCurrent As File
    Dim strFileName As String, strFileFullName As String
    Application.Volatile
    With Application.Caller.Parent.Parent
    strFileFullName = .FullName
    strFileName = .Name
    End With
    Set fsoTemp = New FileSystemObject
    Set filCurrent = fsoTemp.GetFile(strFileName)
    Select Case strReturn
    Case "Name"
    ShowFileAccessInfo = strFileName
    Case "Created"
    ShowFileAccessInfo = filCurrent.DateCreated
    Case "Modified"
    ShowFileAccessInfo = filCurrent.DateLastModified
    Case Else
    ShowFileAccessInfo = "Only use 'Name', 'Created' or 'Modified' as arguments"
    End Select
    Set filCurrent = Nothing
    Set fsoTemp = Nothing
    End Function
    </pre>

    You'll need to set a reference to the Microsoft Script Runtime in the VBEditor (under Tools-References)
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  15. #15
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Document info in a cell (Excel 2000)

    Hi John,
    What OS are you running? I believe that I read some earlier OSes (pre Win95 I think) didn't keep all the file dates that later ones do (might even be a FAT vs FAT32 issue). Though it may just have been that they didn't store all the date/time information, I can't recall.
    As regards the creation date, I think this can be obtained using a few API calls (it appears in Explorer so it must be somewhere!) or possibly even the FileSystemObject but I haven't had a chance to check it out yet.
    Regards,
    Rory

    Microsoft MVP - Excel

Page 1 of 2 12 LastLast

Posting Permissions

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