VBA设置自定义文档属性 [英] VBA Set custom document property

问题描述

我确定可以快速解决问题，因此遇到了一些麻烦.我有一个带有脚本状态"的自定义文档属性字段的Excel文档.脚本状态属性来自从中下载文档的文档库中的一列.我的目标是让用户下载表单，完成分配的任务，然后运行"BeforeSave"宏，该宏将扫描工作并根据宏的结果更新脚本状态属性(即，如果缺少字段，脚本会显示未完成"等).在SharePoint中，这是一个选择下拉框，具有以下选项:未分配，已分配，未完成，已完成/通过，失败，重新测试和&递延.我有检查工作集的逻辑，并且工作正常，只是没有如何更新属性字段.到目前为止，我所拥有的只是:

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:

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!

推荐答案

Sharepoint中的文件可能有一些古怪之处，虽然这不是我所熟悉的，但是通过阅读其他线程我知道这些存在一些困难文件.此处可能是，也可能不是.

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.

在任何情况下，我们都可以尝试对其进行诊断，也许我们将解决问题.

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

正如我在评论中提到的，如果我的工作簿中不存在命名为 DocumentProperty (脚本状态")的脚本，则可以复制此错误.也许这可能像错字一样容易.您可以使用此功能来测试是否存在命名的 DocumentProperty :

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 DocumentProperty exists:

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")，它会返回 True 或 False 的值，具体取决于是否找到命名属性

=CustomPropertyExists("Script Status") and it will return a value of True or False depending on whether the named property is found.

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

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

这篇关于VBA设置自定义文档属性的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！