Reading meta data stored in Word templates on SharePoint

Question

I have a doc library on SharePoint for my work templates (DOTX). The doc library has a column called "Template ID". Each template has an ID stored in that column.

I want to use VBA to loop through each template in the SharePoint doc library and read the value in the Template ID column. If I can read the value, I can do some additional programming with it.

This code works, but it has to open each template to get the value I'm looking for, so it's not a feasible solution:

Sub FeebleAttempt()

Dim TemplateFolder As String
Dim CurrentTemplatePath As String
Dim CurrentTemplate As Document        'Declared as a document so ContentTypeProperties works

TemplateFolder = "\\SharePoint Doc Library\"

CurrentTemplatePath = TemplateFolder & Dir$(TemplateFolder & "*.dotx")

While Len(CurrentTemplatePath) <> 0
    Set CurrentTemplate = Documents.Open(CurrentTemplatePath)

    MsgBox CurrentTemplate.ContentTypeProperties("Template ID").Value

    CurrentTemplate.Close (wdDoNotSaveChanges)
    CurrentTemplatePath = Dir$()
Wend

End Sub

Any ideas?

---

EDIT: Here's the whole picture. My office stores several hundred Word templates on SharePoint. When employees need to use a template, they don't open the DOTX file directly, but click a link that uses code to create a new doc (DOCX) file from the template file they chose. Documents created this way have a built-in reference that points back to the templates stored on SharePoint. This reference is important - a serious problem arises when that reference gets mixed up or deleted, so it needs to be restored.

I want a VBA macro that searches the template doc library for an identifer in the templates. Once that identify is found, the macro will restore the connection with the template containing the correct identifier.

I basically need this:

For each template in "\\sharepoint\template doc library\".templates
    If template.properties("template id").value = brokendoc.properties("template ID").value Then
       brokendoc.attachedtemplate = template.path
    End If
Next

Make sense?

Edit: Here's my solution:

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

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.

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

Answer 1

If the Template ID is a column in the list, why do you need to open the template? I assume you mean the Template ID is a property of the document. I suggest you look into syncing your column values with Word doc properties. Check out this link for more info.

http://weblogs.asp.net/bsimser/archive/2004/11/22/267846.aspx

Once you have the values synced, you can iterate through your document library list items to get the Template Ids without opening the document.

Answer 2

I will say: switch to PowerShell! Now!

I see one other option to retrieve desired document template without opening all documents - use SharePoint List web service: http://<Site>/_vti_bin/Lists.asmx If you call Lists.GetListItems Method and provide CAML query like:

<Query>
   <Where>
      <Eq>
         <FieldRef Name="TemplateID" />
         <Value Type="Text">[Enter your ID]</Value>
      </Eq>
   </Where>
</Query>

Web service will return your item. By reading item fields you can easily find document name and/or some other data needed to build a correct path to document.

I am not VBA expert (never used it) but I found this reference on how to call web service with VBA:

• Calling XML Web Services from Office VBA Using the Web Service References Tool

Hope this answer is helpful in some way - maybe just to give you some different idea on how things can be done. And please switch to PowerShell :)