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.