How to add a DocumentProperty to CustomDocumentProperties in Excel?

by

Try this routine:

Public Sub updateCustomDocumentProperty(strPropertyName As String, _
    varValue As Variant, docType As Office.MsoDocProperties)

    On Error Resume Next
    ActiveWorkbook.CustomDocumentProperties(strPropertyName).Value = varValue
    If Err.Number > 0 Then
        ActiveWorkbook.CustomDocumentProperties.Add _
            Name:=strPropertyName, _
            LinkToContent:=False, _
            Type:=docType, _
            Value:=varValue
    End If
End Sub

Edit: Usage Examples

Five years later and the ‘official’ documentation is still a mess on this… I figured I’d add some examples of usage:

Set Custom Properties

Sub test_setProperties()
    updateCustomDocumentProperty "my_API_Token", "AbCd1234", msoPropertyTypeString
    updateCustomDocumentProperty "my_API_Token_Expiry", #1/31/2019#, msoPropertyTypeDate
End Sub

Get Custom Properties

Sub test_getProperties()
    MsgBox ActiveWorkbook.CustomDocumentProperties("my_API_Token") & vbLf _
        & ActiveWorkbook.CustomDocumentProperties("my_API_Token_Expiry")
End Sub

List All Custom Properties

Sub listCustomProps()
    Dim prop As DocumentProperty
    For Each prop In ActiveWorkbook.CustomDocumentProperties
        Debug.Print prop.Name & " = " & prop.Value & " (" & Choose(prop.Type, _
            "msoPropertyTypeNumber", "msoPropertyTypeBoolean", "msoPropertyTypeDate", _
            "msoPropertyTypeString", "msoPropertyTypeFloat") & ")"
    Next prop
End Sub

Delete Custom Properties

Sub deleteCustomProps()
    ActiveWorkbook.CustomDocumentProperties("my_API_Token").Delete
    ActiveWorkbook.CustomDocumentProperties("my_API_Token_Expiry").Delete
End Sub

More Related Contents:

  1. to make this code simpler
  2. VBA code takes long time to execute in excel
  3. VBA code required for Msg Box when two value equal in two cells
  4. Excel VBA deleting rows in a for loop misses rows
  5. What is the lifetime of a global variable in excel vba?
  6. Download attachment from Outlook and Open in Excel
  7. What is the most efficient/quickest way to loop through rows in VBA (excel)?
  8. Embed picture in outlook mail body excel vba
  9. How to check for empty array in vba macro [duplicate]
  10. Use autofilter on more than 2 criteria
  11. Searching an excel with two search words (multiple lookup) using vba macro [closed]
  12. Excel VBA “Method ‘Document’ of object ‘IWebBrowser2’ failed”
  13. Excel VBA – Combine rows with duplicate values in one cell and merge values in other cell
  14. Load csv file into a VBA array rather than Excel Sheet
  15. Insert line break in wrapped cell via code
  16. Change color of certain characters in a cell
  17. Want VBA in excel to read very large CSV and create output file of a small subset of the CSV
  18. Making VBA Form TextBox accept Numbers only (including +, – and .)
  19. Inserting an Online Picture to Excel with VBA
  20. Change length of For loop while in the loop
  21. VBA – Run Time Error 1004 ‘Application Defined or Object Defined Error’
  22. Consolidating worksheets into one
  23. VBA Macro crashes after 32000 rows
  24. How to create dynamic variable names VBA
  25. How to break a long string into multiple lines
  26. Read VBA macros (or vbaProject.bin) of an Excel file without opening it in MS Excel [closed]
  27. simple vba code gives me run time error 91 object variable or with block not set
  28. How do I open a file if I only know part of the file name?
  29. VBA recognizing workbook by partial name
  30. VBA inheritance, analog of super

Leave a Comment