Blog by PQR

It was long overdue but we finally made the move away from Visual Basic for Applications (Microsoft Office scripting). We implemented Sharepoint 2010 and were still using the old fashioned VBA code that originated from Office 2003. So we got Visual Studio out and started translating the VBA code into Visual Basic dotnet.

After the code was translated and connections to CRM were setup, we bound the new standard document to the sharepoint libraries. Now if we create a new document from the website or sharepoint workspace, it’s always setup in our template style and is pretty easy to maintain since the VSTO installer checks for updates all by itself.

The designer and dotnet way of handling the ribbon and the document makes it very easy to create templates in Visual Studio. Once little thing remained though.

Our new template contains a custom ribbon and some homemade styles. When it’s started, a form appears requesting some information, starting with a document category. Once a category is chosen, a master layout document is imported. Optionally, you can add building blocks into the new document, filtered by the choice of category. This allows our people to quickly create default documents with all the information from the input form updated automatically in the document and imported building blocks (customer names, authors, versions, dates, ..).

So our new documents are built from 3 different parts; a code part, a layout part and one or more building block parts. That way any change only need to be made in one spot. pretty neat

But simply reordering the old building blocks to the new style turned out to be a bit of a pain. Because we used VBA and custom ribbon xml, the old documents kept asking for credentials from the old Sharepoint because it had all kinds of internal references to that site. So I created a small powershell script that changed the current attached template to point to our new template;

$Word = new-object -comobject word.application
$word.visible = $false

$templ = ("C:\programdata\huisstijl\Templates\standaard document.dotx")
$docs = get-childitem -recurse -include *.doc*

$docs | % {
  $_.fullname
  $d = $word.documents.open($_.fullname)
  $xml = [xml]$d.WordOpenXML
  $xml.GetElementsByTagName("Relationship") | Where {$_.Type -like "*attachedTemplate"} | Select Target
  $d.attachedTemplate = $templ
  $d.save()
  $d.close()
}

$word.quit()

This script is actually more complex than it needs to be. The point is that the current template only shows correctly when it can actually be found. If not, it will show the normal.dotx location. To get the configured template you need to open the document as XML and search for a relationship called attachedTemplate. That points to the actual configured template. Microsoft never ceases to amaze me in that sense.

But unfortunately, that was not enough. The ribbon also had some references in it. And because we built a new ribbon, when opening a building block, the PQR ribbon tab showed twice. Removing the custom ribbon can become a bit of a hassle. So I created a new script that rebuilt the whole document set by simply selecting the whole content from a building block and create a new document with the new template and copy it in;

[ref]$SaveFormat = "microsoft.office.interop.word.WdSaveFormat" -as [type]
$oMissing = [System.Reflection.Missing]::Value

$Word = new-object -comobject word.application

$root = "C:\programdata\huisstijl\"
$dest = "D:\huisstijl\bouwblokken\"

$templ = ($root + "templates\standaard document.dotx")

$docs = get-childitem ($root + "bouwblokken") -recurse -include *.doc*

$docs | % {
  $word.visible = $False
  $d = $word.documents.open($_.fullname, $oMissing, $true)
  $n = $word.documents.add($templ)
  $sa = $dest + $_.directory.name + "\" + $_.basename + ".docx"
  "Processing : " + $sa
 
  if (! (test-path ($dest + $_.directory.name ) ) ) { mkdir ( $dest + $_.directory.name ) }
 
  $d.activewindow.selection.wholestory()
  $d.activewindow.selection.copy()
  $n.activewindow.selection.paste()
 
  $n.saveas([ref]$sa, [ref]$SafeFormat::wdFormatDocument)
  $n.close()
  $d.close()
}

$word.quit()

And that’s it. It opens the documents one by one (readonly), selects everything, copy it to clipboard, paste it in a new document and save that at a new location. No more old references, old style ‘doc’ files, it’s all fresh and clean now.

Powershell and VSTO are pretty cool