Solved: Using VBA to read the metadata or file properties of files in a SharePoint library

[rgmatthes]

(Mods, please delete http://www.vbaexpress.com/forum/showthread.php?t=39072. This topic clarifies my problem.)

I have a few hundred Word templates (DOTX) on a SharePoint site. Many teams of users work with these templates.

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

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.

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.

I’m basically looking for this:

[VBA]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[/VBA]

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

Any ideas? Could you point me in the right direction?

[Frosty]

I haven't worked out the kinks to checking document properties when the document is closed. Here are a couple of links which may help (I was curious, and so did a couple of searches).

It doesn't appear as if any of the standard stuff will let you read custom document properties (if you were using builtin properties, it might be a little simpler).

Maybe someone else will have a real answer, but for now... all I can do is point you in the direction I would start looking. Sorry it's not more helpful.

http://support.microsoft.com/default...b;en-us;224351
http://technet.microsoft.com/library/ee692828.aspx

Just as a warning, it looks like there are a number of issues with using DSOFile.dll, depending on which OS you're using, but it seems that this is the way to go about reading custom document properties of unopen office files.

[rgmatthes]

Frosty, that was plenty helpful. Thank you!

Here's what I came up with:

[vba]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[/vba]

Apparently I don't need to add any DSO references for this to work... but maybe I'm missing something.

Also, a warning: a colleague has informed me that this might not work over https:\\ (SSL). Maybe there's a better solution out there, but this works for me at the moment.

Again, thank you for your help, Frosty!

[Frosty]

Wow, seemed easier than I thought it'd be. Glad to be of marginal help.
My machine fails on the CreateObject line... probably because I didn't download the DSOFile.dll.

It still seems like you're opening the document-- are you sure you need to?

Interesting note about using SSL.

Just one further comment-- I wouldn't use End... ever. This is a better/safer way to construct your code using GoTo instead of End, and doing some basic garbage collection ("garbage collection" is much more important when working with objects and GetObject/CreateObject than at other times).
[vba]
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("C:\Temp\").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
GoTo l_exit
End If

Next

MsgBox ("No matching template found. Please attach the proper template manually."), vbCritical

l_exit:
Set FSO = Nothing
Set objFile = Nothing
Set objDSO = Nothing
End Sub
[/vba]
Without actually being able to test the code, I can't do any more in terms of clean up of the code.. but I would suggest not having your CreateObject inside of a For Loop. You should be to create it outside of the For Loop, and then simply use the .Open command within the loop (at the top) and probably something like .Close (at the bottom).

Almost certainly your code will fail when it's used in more than proof-of-concept mode (i.e., multiple templates, etc), as well as the very big "No No" of using End in code (which halts ALL execution of code... not just exiting the current procedure -- you can break a lot of things you don't want to when you use End).