vba - 如何在 Excel 中将 DocumentProperty 添加到 CustomDocumentProperties？

Question

我正在尝试将 DocumentProperty 添加到 CustomDocumentProperties 集合中。代码如下:

Sub testcustdocprop()
Dim docprops As DocumentProperties
Dim docprop As DocumentProperty

Set docprops = ThisWorkbook.CustomDocumentProperties
Set docprop = docprops.Add(Name:="test", LinkToContent:=False, Value:="xyz")

End Sub

运行此命令会出现以下错误:

Run-time error '5':
Invalid procedure call or argument

我尝试使用 .Add 作为 void 函数运行它，如下所示:

docprops.Add Name:="test", LinkToContent:=False, Value:="xyz"

这给了我同样的错误。如何添加自定义文档属性？

Answer 1

尝试这个例程:

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

<小时/>

编辑:使用示例

^{五年后，“官方”文档对此仍然一团糟......我想我应该添加一些用法示例:}

设置自定义属性

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

获取自定义属性

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

列出所有自定义属性

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

删除自定义属性

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