Automation, PowerShell and Word Templates - let the technician do tech
In my work as a Database Consultant, I enjoy technical challenges, but it's always a painful process when I need to transfer my technical knowledge into a report. Writing the report isn't really the difficult part; the real challenge is making it accessible for those who don't have the same degree of technical knowledge. Part of this can be minimized by creating report templates. Let's say that you need to create a performance analysis to identify and report problems and recommend solutions. By creating a report template, with the basics already filled in, then you only need to add the technical information to the template, interpret the results and share your conclusions. Using a template makes the process less painful and faster. You will also need a standard template to be reused when you have to perform a similar review for other servers or customers. However, collecting the data, adding it to the template and formatting it, is still something that may take a lot of time, and it is not the most exciting task for a highly technical person. As someone who solves problems for a living, I had a thought "why can't we just automate it?".
"Automation" isn't just a buzzword
With Microsoft supporting the open-source community more than ever, projects like the outstanding dbatools are popping up and getting stronger. PowerShell is our best friend for this challenge and the rule is always the same: if the probability to repeat a task is high, let's automate it! [bctt tweet="'Automation' isn't just a buzzword." username="pythian"] Sometimes prepare all the automation can take a long time; just remember, this is an investment in the future...An "automation" real case
Last year we worked on a project where several SQL Server boxes were to be deployed. Some like a simple standalone and others with AlwaysOn Availability Groups feature ready to be used. In the worst-case scenario the following tasks could be performed (after having the base VMs deployed):- Format the SQL Server disks in 64K
- Configure the best practices for the OS
- Create a two-node Windows Server Failover Cluster
- Install a standalone SQL Server instance on both servers
- Configure SQL Server base on the best practices
- Enable AlwaysOn AG and create a group with a listener and an IP
Going back to the report
Ok, now that I proved that automation is the way to go, let's go back to the report issue: How did I solve the problem? Let's summarize it...- Objective: Find a way to insert data into the Word template.
- Used Technology: Powershell
- Strategy: We have a Word template with the common report text. We need to complete it by placing the results of the collected data on specific zones of the template.
How can I edit a Word file using PowerShell?
After some research I found out that we can create a COM Object based on Word.Application, by using the following function you can open a Word file:Function OpenWordDoc($Filename) { $Word=NEW-Object –comobject Word.Application Return $Word.documents.open($Filename) }With the document opened, I could perform all of the necessary changes, save the file and use the following function:
Function SaveAsWordDoc($Document, $FileName) { $Document.Saveas([REF]$Filename) $Document.close() }But what about the document changes? I found two challenges: how to replace a text tag (like <TAG_YOUR_NAME>) and how to add an image on a specific location. Here's how I did it: Replacing a text tag: This one was simple. I just wanted to identify specific text in the document and replace all the instances with other text. In order to achieve that I used the following function.
Function ReplaceTag($Document, $FindText, $ReplaceWithText) { $FindReplace=$Document.ActiveWindow.Selection.Find $matchCase = $false; $matchWholeWord = $true; $matchWildCards = $false; $matchSoundsLike = $false; $matchAllWordForms = $false; $forward = $true; $format = $false; $matchKashida = $false; $matchDiacritics = $false; $matchAlefHamza = $false; $matchControl = $false; $read_only = $false; $visible = $true; $replace = 2; $wrap = 1; $FindReplace.Execute($findText, $matchCase, $matchWholeWord, $matchWildCards, $matchSoundsLike, $matchAllWordForms, $forward, $wrap, $format, $replaceWithText, $replace, $matchKashida ,$matchDiacritics, $matchAlefHamza, $matchControl) | Out-Null }Adding an image: I spent quite some time trying to find a way to achieve this. Basically, I wanted to add an image from a file located on my computer, in a specific place. I tried to create a text tag and replaced it, but it wasn't working. So I found a way to add images to a Word document using the method $Document.ActiveWindow.Selection.InlineShapes.AddPicture(). This partially solved my problem. With this method I could add an image in the document, in the location of the pointer. How did I solve this? As creating text tags were not the way to go, I learned that I could achieve this by creating bookmarks (in Word, go to Insert->Bookmarks); this way I could move the pointer to a bookmark and then add the image. Here is the function to do this:
Function AddImage($Document, $BookmarkName, $ReplaceWithImage) { $FindReplace=$Document.ActiveWindow $FindReplace.Selection.GoTo(-1,0,0,$Document.Bookmarks.item("$BookmarkName")) $FindReplace.Selection.InlineShapes.AddPicture("$replacewithImage") }Note that the bookmark won't be removed once you add the image. If you try to add multiple images on the same bookmark, it will work. Now that we have the main functions, how can we make this work altogether? This is simple:
$TemplateFile = "C:\reports\Template_Report.docx" $FinalFile = "C:\reports\FinalReport.docx" # Open template file $Doc=OpenWordDoc -Filename $TemplateFile # Replace text tags ReplaceTag –Document $Doc -FindText '<client_name>' -replacewithtext "Pythian" ReplaceTag –Document $Doc -FindText '<server_name>' -replacewithtext "WINSRV001" # Add image AddImage –Document $Doc -BookmarkName 'img_SomeBookmark' -ReplaceWithImage "C:\reports\img.png" # Save FInal Report SaveAsWordDoc –document $Doc –Filename $FinalFile
