none
CustomDocumentProperties and Macro Location RRS feed

  • Question

  • I found CustomDocumentProperties and just a little bit of doc on it.  I am confused about a few things.

    1) Are the properties associated with the .xls file or with a particular sheet?

    2) If the user is editing A.xls, and uses macro WhatEver from Personal.xls, and WhatEver sets some CustomDocumentProperties, are the values associated with A.xls or Personal.xls? 

    and 3) Can the values be displayed?  It looks like I can display something called Properties from either the Excel window or the VBA window but those displays don't distinguish between Custom and BuiltIn properties and there are both kinds of properties, right?  So that's confusing.

    Thanks,  Bob

    Sunday, March 4, 2012 5:39 PM

Answers

  • 1) With the workbook (.xls), not with any individual sheet.

    2) The macro *must* specify what CustomDocumentProperties belongs to, otherwise it won't work. For example, if it refers to ActiveWorkbook.CustomDocumentProperties, it'll set the document properties in the currently active workbook, and if it refers to ThisWorkbook.CustomDocumentProperties, it'll set them in the workbook that contains the code (Personal.xls).

    3) Select File | Properties in Excel and activate the Custom tab to view the custom document properties.


    Regards, Hans Vogelaar

    • Proposed as answer by Rod Gill Sunday, March 4, 2012 7:40 PM
    • Marked as answer by eBob.com Monday, March 5, 2012 9:37 PM
    Sunday, March 4, 2012 5:49 PM
  • In 2007, click the Office button, then Prepare.

    Click Properties, then click the arrow next to Document Properties and select Advanced Properties.


    Regards, Hans Vogelaar

    • Proposed as answer by danishaniModerator Monday, March 5, 2012 6:01 PM
    • Marked as answer by eBob.com Monday, March 5, 2012 9:37 PM
    Monday, March 5, 2012 4:58 PM
  • Use code like this:

    Sub Proper_VBAtools()
        With ActiveWorkbook
        .BuiltinDocumentProperties("Author").value = "Youre Name"
        .BuiltinDocumentProperties("Manager").value = "Whatever"
        .BuiltinDocumentProperties("Title").value = "Title"
        .BuiltinDocumentProperties("Subject").value = "Subject"
        .BuiltinDocumentProperties("Company").value = "Yours or not"
        .BuiltinDocumentProperties("Category").value = "Raport"
        .BuiltinDocumentProperties("Last Author").value = "Last Modyf. by OShon"
        .BuiltinDocumentProperties("Revision Number").value = "1"
        .BuiltinDocumentProperties("Comments").value = "VBSpecialist: MVP Shon Oskar " & vbCr & _
        "www.vbatools.pl
        End With
    End Sub

    As Hans write, assign this to youre personal.xlsm file and use from time to time.


    Oskar Shon, Office System MVP

    Press if Helpful; Answer when a problem solved

    • Marked as answer by eBob.com Monday, March 5, 2012 9:45 PM
    Monday, March 5, 2012 8:16 PM
    Answerer

All replies

  • 1) With the workbook (.xls), not with any individual sheet.

    2) The macro *must* specify what CustomDocumentProperties belongs to, otherwise it won't work. For example, if it refers to ActiveWorkbook.CustomDocumentProperties, it'll set the document properties in the currently active workbook, and if it refers to ThisWorkbook.CustomDocumentProperties, it'll set them in the workbook that contains the code (Personal.xls).

    3) Select File | Properties in Excel and activate the Custom tab to view the custom document properties.


    Regards, Hans Vogelaar

    • Proposed as answer by Rod Gill Sunday, March 4, 2012 7:40 PM
    • Marked as answer by eBob.com Monday, March 5, 2012 9:37 PM
    Sunday, March 4, 2012 5:49 PM
  • Thank you very much Hans.  Should I be able to find File | Properties in 2007?  I haven't been able to.  I do find Properties on the Developer ribbon but that leads to a display which does  not distinguish between Builtin and Custom.

    Thanks again,  Bob

    Monday, March 5, 2012 4:29 PM
  • In 2007, click the Office button, then Prepare.

    Click Properties, then click the arrow next to Document Properties and select Advanced Properties.


    Regards, Hans Vogelaar

    • Proposed as answer by danishaniModerator Monday, March 5, 2012 6:01 PM
    • Marked as answer by eBob.com Monday, March 5, 2012 9:37 PM
    Monday, March 5, 2012 4:58 PM
  • Monday, March 5, 2012 5:07 PM
  • The Backstage is Office 2010 - eBob.com asked about 2007...

    Regards, Hans Vogelaar

    Monday, March 5, 2012 5:57 PM
  • Use code like this:

    Sub Proper_VBAtools()
        With ActiveWorkbook
        .BuiltinDocumentProperties("Author").value = "Youre Name"
        .BuiltinDocumentProperties("Manager").value = "Whatever"
        .BuiltinDocumentProperties("Title").value = "Title"
        .BuiltinDocumentProperties("Subject").value = "Subject"
        .BuiltinDocumentProperties("Company").value = "Yours or not"
        .BuiltinDocumentProperties("Category").value = "Raport"
        .BuiltinDocumentProperties("Last Author").value = "Last Modyf. by OShon"
        .BuiltinDocumentProperties("Revision Number").value = "1"
        .BuiltinDocumentProperties("Comments").value = "VBSpecialist: MVP Shon Oskar " & vbCr & _
        "www.vbatools.pl
        End With
    End Sub

    As Hans write, assign this to youre personal.xlsm file and use from time to time.


    Oskar Shon, Office System MVP

    Press if Helpful; Answer when a problem solved

    • Marked as answer by eBob.com Monday, March 5, 2012 9:45 PM
    Monday, March 5, 2012 8:16 PM
    Answerer
  • Thank you again Hans.

    Bob

    Monday, March 5, 2012 9:38 PM
  • Thank you Oskar.

    Bob

    Monday, March 5, 2012 9:46 PM