Results 1 to 9 of 9
  1. #1
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    XL Properties?! (XL97/WinNT4)

    I have the following little macro for setting up and assigning properties in Word:

    Sub ManualAddProperties()
    With ActiveDocument
    .AttachedTemplate = "t:itprocnew.dot"
    .CustomDocumentProperties.Add Name:="ReviewType", LinkToContent:=False, _
    Value:="On change", Type:=msoPropertyTypeString
    .CustomDocumentProperties.Add Name:="ReviewNever", LinkToContent:=False, _
    Value:=False, Type:=msoPropertyTypeBoolean
    .CustomDocumentProperties.Add Name:="ReviewOnChange", LinkToContent:=False, _
    Value:=True, Type:=msoPropertyTypeBoolean
    .CustomDocumentProperties.Add Name:="NextReviewDate", LinkToContent:=False, _
    Value:="30/12/1899", Type:=msoPropertyTypeDate
    .BuiltinDocumentProperties("Author") = " "
    .BuiltinDocumentProperties("Title") = " "
    .BuiltinDocumentProperties("Company") = " "
    .BuiltinDocumentProperties("Category") = " "
    .BuiltinDocumentProperties("Comments") = " "
    End With
    End Sub

    But when I went to convert it to XL I simply couldn't find anything to tell me how to go about it! Looking up help (in XL) brought up how to do it in Word and in Access, but not XL! Needless to say, a simple exchange of the word 'document' for the word 'workbook' had no effect.

    I would greatly appreciate some pointers on this one!
    Beryl M


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

    Re: XL Properties?! (XL97/WinNT4)

    BuiltInDocumentProperties and CustomDocumentProperties exist in Excel too, as properties of the Workbook object. The active workbook is, not surprisingly, ActiveWorkbook. A workbook does not have an AttachedTemplate property, since an Excel workbook has no link whatsoever to the template used to create it from.

  3. #3
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: XL Properties?! (XL97/WinNT4)

    Good Lord - after all this time of trying to remember that XL uses workbooks, worksheets and cells and Word uses documents, pages and paragraphs - all of a sudden it doesn't!

    Do you know, it didn't even occur to me that it might be EXACTLY the same? Apart from the activedocument vs activeworkbook bit, of course.

    Thanks, Hans <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    Beryl M


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

    Re: XL Properties?! (XL97/WinNT4)

    Don't you just <img src=/S/love.gif border=0 alt=love width=15 height=15> the consistency? <img src=/S/innocent.gif border=0 alt=innocent width=20 height=20>

  5. #5
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: XL Properties?! (XL97/WinNT4)

    That's one way to put it ... <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>

    How would one display the properties in a cell on the worksheet, though? And I know this one isn't the same because 'form fields' is not on the Insert menu at all! I couldn't find anything in formulae, either, but that might be because I don't really know what I'm looking for there ... <img src=/S/blush.gif border=0 alt=blush width=15 height=15>

    Thanks again!
    Beryl M


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

    Re: XL Properties?! (XL97/WinNT4)

    Excel does not have the concept of "fields". You will have to create user-defined functions in a module, for example:

    Function GetBuiltinProperty(PropertyName As String) As Variant
    Application.Volatile
    GetBuiltinProperty = ThisWorkbook.BuiltinDocumentProperties(PropertyNam e).Value
    End Function

    Function GetCustomProperty(PropertyName As String) As Variant
    Application.Volatile
    GetCustomProperty = ThisWorkbook.CustomDocumentProperties(PropertyName ).Value
    End Function

    Use like this in a cell:

    =GetBuiltinProperty("Author")
    or
    =GetCustomProperty("ReviewType")

  7. #7
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: XL Properties?! (XL97/WinNT4)

    Hey - that's clever! <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    Is there something nice and simple like that, that can be used in a Word document to put the property in a table cell? I've just got some code that operates on doc.close at the moment, but that would be much simpler ...
    Beryl M


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

    Re: XL Properties?! (XL97/WinNT4)

    Word has the DocProperty field that can be used for both built in and custom document properties. Select Insert | Field, select DocProperty from the list of available fields, and select or enter the property name (I forgot which in Word 97), then click OK. Alternatively, type Ctrl+F9 to insert field braces { } , type DocProperty Author (or whatever the name of the property is), then press Shift+F9 to hide the field code, and press F9 to update.

  9. #9
    3 Star Lounger MacroAlan's Avatar
    Join Date
    Feb 2003
    Location
    St Louis, Missouri, USA
    Posts
    254
    Thanks
    6
    Thanked 1 Time in 1 Post

    Re: XL Properties?! (XL97/WinNT4)

    Also, look at http://www.barasch.com/excel/ACu15293.htm for info on using the Custom Properties. We defined a whole bunch in our project and pull them out for many uses.
    Alan

Posting Permissions

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