Databases
StackOverflow2013
Postsdbo
POUsing VBA to read the metadata or file properties of files in a SharePoint doc library
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POUsing VBA to read the metadata or file properties of files in a SharePoint doc library
    primarykey
    data
    text
    <p>I have a few hundred Word templates (DOTX) on a SharePoint site. Many teams of users work with these templates.</p> <p>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”. </p> <p>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.</p> <p>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.</p> <p>I’m basically looking for this:</p> <pre><code>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 </code></pre> <p>…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.</p> <p>Any ideas? Could you point me in the right direction?</p> <p>Edit: Here's my solution:</p> <pre><code>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 </code></pre> <p>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.</p> <p>Also, this might not work over https:// (SSL). Worked for me though, so I thought I'd share.</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USrgmatthes
    1. USrgmatthes
    plurals
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POUsing VBA to read the metadata or file properties of files in a SharePoint doc library
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POUsing VBA to read the metadata or file properties of files in a SharePoint doc library
      1. This table or related slice is empty.
      1. VTUpMod
    1. COJust make sure you do not create your template ID with a space e.g. 'Template ID' as it is converted to 'Template_x0020_ID' for the internal name
      singulars
      1. POUsing VBA to read the metadata or file properties of files in a SharePoint doc library
      1. USNat
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload