使用VBA读取SharePoint文档库中文件的元数据或文件属性 [英] Using VBA to read the metadata or file properties of files in a SharePoint doc library

问题描述

我在SharePoint网站上有几百个Word模板(DOTX).许多用户团队都在使用这些模板.

I have a few hundred Word templates (DOTX) on a SharePoint site. Many teams of users work with these templates.

当用户需要自定义此文档时，他们单击SharePoint上的特殊链接以从他们选择的模板中生成一个新文档(DOCX).始终需要将此新文档文件链接"回其在SharePoint上的模板文件.如果文档丢失该链接，则它将无法正常工作，并被视为损坏".

When a user needs to customize this documentation, they click a special link on SharePoint to generate a new document (DOCX) from the template they choose. This new document file always needs to be "linked" back to its template file on SharePoint. If the document loses that link, it won’t work correctly and is considered "broken".

文档断开时，我需要重新建立指向SharePoint上正确模板的链接.以编程方式执行此操作很有意义，因此我可以将解决方案分发给我的团队.

When documents break, I need to re-establish the link back to the right template on SharePoint. It makes sense to do this programmatically so I can distribute the solution to my team.

我想给每个模板文件一个唯一的模板ID(一个三位数字)，该ID存储在元数据或自定义文件属性中.从模板生成新文档后，模板ID会自动保存到文档中，因此已设置好.现在，我只需要使用VBA扫描SharePoint文档库中的模板文件以查找匹配的模板ID.找到该链接后，我可以重新建立链接，一切正常.

I want to give each template file a unique Template ID (a three-digit number), stored in metadata or a custom file property. When new documents are generated from the templates, the Template ID automatically carries over into the document, so that’s set. Now I just need to use VBA to scan the template files in the SharePoint document library for the matching Template ID. When that’s found, I can re-establish the link and all is well.

我基本上是在寻找这个

Sub DocFixer()

Dim objTemplate as Template
Dim objBrokenDoc as Document

Set objBrokenDoc = ActiveDocument

For each objTemplate in "\\SharePoint\Template Library\".Templates
    If objTemplate.Properties("Template ID").Value = objBrokenDoc.Properties("Template ID").Value Then
        objBrokenDoc.AttachedTemplate = objTemplate.Path
        Exit For
    End If
Next

End Sub

…但是我在使用VBA读取SharePoint文档库内容而不真正打开内容时遇到了麻烦，因为使用这么多的模板花费的时间太长了，而且对用户造成很大的破坏性.

…but I’m having trouble using VBA to read SharePoint doc library contents without actually opening the contents, as that takes far too long with so many templates, plus its very disruptive for the user.

有什么想法吗?你能指出我正确的方向吗?

Any ideas? Could you point me in the right direction?

这是我的解决方案:

Sub Macro()

Dim FSO As Object
Set FSO = CreateObject("Scripting.FileSystemObject")

Dim objFile As Object
Dim objDSO As Object

For Each objFile In FSO.GetFolder("\\SharePoint\doc lib\").Files
    Set objDSO = CreateObject("DSOFile.OleDocumentProperties")
    objDSO.Open objFile.Path

    If objDSO.CustomProperties.Item("Template_ID") = ActiveDocument.CustomDocumentProperties("Template_ID").Value Then
        ActiveDocument.AttachedTemplate = objFile.Path
        End
    End If
Next

MsgBox ("No matching template found. Please attach the proper template manually."), vbCritical

End Sub

显然，这可以利用DSOFile.dll(http://technet.microsoft.com/zh-cn/library/ee692828.aspx)，但我不必添加引用吗?仍然对此感到困惑.

Apparently this taps into DSOFile.dll (http://technet.microsoft.com/en-us/library/ee692828.aspx), but I didn't have to add the reference? Still confused on that part.

此外，这可能不适用于https://(SSL).不过为我工作，所以我想分享一下.

Also, this might not work over https:// (SSL). Worked for me though, so I thought I'd share.

推荐答案

我首先调用

I would start by calling the SharePoint web services from VBA. Once there you can make a call to GetListItems that will pull back the document with the correct TemplateID attribute directly.

这篇关于使用VBA读取SharePoint文档库中文件的元数据或文件属性的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！