title: Using VBA to read the metadata or file properties of files in a SharePoint doc library
body: <p>I have a few hundred Word templates (DOTX) on a SharePoint site. Many teams of users work with these templates.</p>
  
  <p>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”. </p>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <p>I’m basically looking for this:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>…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.</p>
  
  <p>Any ideas? Could you point me in the right direction?</p>
  
  <p>Edit: Here's my solution:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>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.</p>
  
  <p>Also, this might not work over https:// (SSL). Worked for me though, so I thought I'd share.</p>