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
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
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.