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.

Can I ask, why VBA? Depending on what you want to do, powershell or vb.net would probably be better approaches. — John Koerner
– John Koerner, Commented 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 — Anders Rask
– Anders Rask, Commented 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
– rgmatthes, Commented Sep 19, 2011 at 21:25

user602441 · Accepted Answer · 2011-09-16 22:29:23Z

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.

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
– rgmatthes, Commented Sep 19, 2011 at 21:25

Vedran Rasol · Accepted Answer · 2011-09-19 22:00:26Z

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

Stack Exchange Network

Reading meta data stored in Word templates on SharePoint

2 Answers 2

Hot Network Questions

Reading meta data stored in Word templates on SharePoint

2 Answers 2

Related

Hot Network Questions