VBA 设置自定义文档属性

VBA Set custom document property

提问by NMacioce

I am having some trouble with what I am sure is a quick fix. I have an excel doc with a custom document property field of "Script Status". The script status property comes from a column in the document library from which the doc was downloaded from. My goal is to have a user download the form, complete their assigned task, and then have a "BeforeSave" macro run that will scan the work and update that script status property based on the results of the macro (i.e. if fields are missing, script will say "Not Completed" etc.). In SharePoint, this is a choice dropdown box with the options of: Not Assigned, Assigned, Not Completed, Completed/Pass, Fail, Re-Test, & Deferred. I have the logic on checking the work set and works fine, just not how to update the property field. What I have for that so far is simply:

我遇到了一些我确信是快速修复的问题。我有一个带有“脚本状态”的自定义文档属性字段的 excel 文档。脚本状态属性来自文档库中从中下载文档的一列。我的目标是让用户下载表单，完成他们分配的任务，然后运行“BeforeSave”宏，该宏将扫描工作并根据宏的结果更新该脚本状态属性（即，如果缺少字段，脚本会说“未完成”等）。在 SharePoint 中，这是一个选择下拉框，其中包含以下选项：未分配、已分配、未完成、已完成/通过、失败、重新测试和推迟。我有检查工作集的逻辑并且工作正常，只是不知道如何更新属性字段。到目前为止，我所拥有的只是：

Application.ThisWorkbook.CustomDocumentProperties.Item("Script Status").Value = "Fail"

Once it runs, I get an error to the line stating "invalid procedure call or arguement". I have tried to research proper syntax for this line but have been to no avail. Any help would be grateful!

一旦它运行，我就会收到一条错误消息，指出“无效的过程调用或争论”。我试图研究这一行的正确语法，但无济于事。任何帮助将不胜感激！

采纳答案by David Zemens

There may be some quirk about files from Sharepoint, admittedly this is not something I am familiar with, but from reading other threads I know there are some difficulties with these files. This may or may not be the case, here.

Sharepoint 中的文件可能有一些怪癖，诚然这不是我熟悉的东西，但是通过阅读其他线程，我知道这些文件存在一些困难。这可能是也可能不是，在这里。

In any case, we can try to diagnose it and perhaps we will resolve the problem.

无论如何，我们可以尝试诊断它，也许我们会解决问题。

As I mentioned in the comments, I can replicate this error if the named DocumentProperty("Script Status") doesn't exist in my workbook. This could be as easy as a typo, perhaps. You can use this function to test whether a named DocumentPropertyexists:

正如我在评论中提到的，如果DocumentProperty我的工作簿中不存在命名（“脚本状态”），我可以复制此错误。这可能就像打字错误一样简单。您可以使用此函数来测试命名是否DocumentProperty存在：

Function CustomPropertyExists(propName As String) As Boolean
    Dim wb As Workbook
    Dim docProp As DocumentProperty
    Dim propExists As Boolean
    Set wb = Application.ThisWorkbook
    For Each docProp In wb.CustomDocumentProperties
        If docProp.Name = propName Then
            propExists = True
            Exit For
        End If
    Next
    CustomPropertyExists = propExists
End Function

If you put this in a standard module, you can call the function from the Worksheet like:

如果将它放在标准模块中，则可以从工作表中调用该函数，例如：

=CustomPropertyExists("Script Status")and it will return a value of Trueor Falsedepending on whether the named property is found.

=CustomPropertyExists("Script Status")它将根据是否找到命名属性返回True或的值False。

You could call it from a subroutine, e.g:

您可以从子程序调用它，例如：

If CustomPropertyExists("Script Status") Then
    MsgBox "Exists!",vbInformation
Else 
    MsgBox "Does not exist", vbCritical
End If

回答by NMacioce

It appears that the Sharepoint property associated with the excel doc is neither a CustomDocumentProperty nor a BuiltInDocumentProperty. Upon dabbling some more with the code, the Sharepoint fields are "ContentTypeProperty". Using the same code posted in the original question with ContentTypeProperty instead of the custom property, the code worked successfully.

与 Excel 文档关联的 Sharepoint 属性似乎既不是 CustomDocumentProperty 也不是 BuiltInDocumentProperty。在进一步研究代码后，Sharepoint 字段是“ContentTypeProperty”。使用原始问题中发布的相同代码与 ContentTypeProperty 而不是自定义属性，代码成功运行。

Please reference David's code to determine if your "property" is truly a custom property and not a content type. That was very helpful!

请参考 David 的代码来确定您的“属性”是否真的是自定义属性而不是内容类型。那很有帮助！

回答by thestas

I Don't know if this will help anyone, but I was working on saving an .xlsm file to Sharepoint site, which has has Custom Properties set. I could not find a solution but I managed to sort it out using this.

我不知道这是否对任何人有帮助，但我正在努力将 .xlsm 文件保存到 Sharepoint 站点，该站点设置了自定义属性。我找不到解决方案，但我设法使用它解决了这个问题。

Private Sub SaveToSharePoint()
'set the save location of the document and name
Dim FolderPath As String: FolderPath = "//sharepoint.com/sites/Shared Documents/"
Dim Type As String: Type = "Doc"
Dim CurrentYear As String: CurrentYear = CStr(Year(Date))
Dim FileName As String: FileName = Type & "_" & CurrentYear & ".xlsm"
Dim FullFilePath As String: FullFilePath = FolderPath + FileName

'Creates the initial file and saves it as .xlsm
On Error Resume Next
ThisWorkbook.SaveAs FullFilePath, FileFormat:=52

'Sets the custom properties
ThisWorkbook.ContentTypeProperties("Type").Value = Type
ThisWorkbook.ContentTypeProperties("Year").Value = CurrentYear

'Updates the file
On Error Resume Next
ThisWorkbook.SaveAs FullFilePath, FileFormat:=52 End Sub