locked
Using VBA to read the metadata or file properties of files in a SharePoint doc library RRS feed

  • Question

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

    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 

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

    • Moved by Mike Walsh FIN Saturday, September 17, 2011 4:57 PM not general (From:SharePoint - General Question and Answers and Discussion (pre-SharePoint 2010))
    • Edited by rgmatthes Monday, September 19, 2011 8:54 PM clarification
    Saturday, September 17, 2011 2:53 PM

Answers

  • Hi all,

    I got it to work like this:

    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
    
    

    This uses what I was told to be DSOFile.dll, though apparently I didn't need to add it as a reference.
    http://technet.microsoft.com/en-us/library/ee692828.aspx

    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.


    • Marked as answer by rgmatthes Wednesday, September 21, 2011 8:18 PM
    • Edited by rgmatthes Wednesday, September 21, 2011 8:19 PM
    Wednesday, September 21, 2011 8:18 PM

All replies

  • If I understand your requirement correctly, you need to read files in a SharePoint library which has a column called "Template ID".

    http://msdn.microsoft.com/en-us/library/ms457534.aspx


    Steve Thomas
    Sunday, September 18, 2011 4:23 AM
  • Thank you, but that's for VB.NET. I'm really trying for VBA here!

    Monday, September 19, 2011 8:48 PM
  • Hi all,

    I got it to work like this:

    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
    
    

    This uses what I was told to be DSOFile.dll, though apparently I didn't need to add it as a reference.
    http://technet.microsoft.com/en-us/library/ee692828.aspx

    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.


    • Marked as answer by rgmatthes Wednesday, September 21, 2011 8:18 PM
    • Edited by rgmatthes Wednesday, September 21, 2011 8:19 PM
    Wednesday, September 21, 2011 8:18 PM