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$()

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

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 If

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.

  • Can I ask, why VBA? Depending on what you want to do, powershell or vb.net would probably be better approaches. Sep 17, 2011 at 1:07
  • @rgmatthes please refrain from using Answers to comment on responses. Either add to your original question or add a comment to an answer please Sep 19, 2011 at 21:17
  • Thanks for the response. VBA is best for my team and environment right now... that might change next year, but it's the best for now. Could you help me?
    – rgmatthes
    Sep 19, 2011 at 21:25

2 Answers 2


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.


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

  • I don't want to open the templates during this process - I'm actually trying to avoid that. I want a VBA macro that searches the template doc library for an identifer in the templates. Once that identifier is found, the macro will restore the connection with the template containing the correct identifier. Make sense? Coud you perhaps elaborate on your last point?
    – rgmatthes
    Sep 19, 2011 at 21:25

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:

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

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:

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 :)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.