Automation, PowerShell and Word Templates - let the technician do tech

4 min read

Jul 27, 2017 12:00:00 AM

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

How long would you take to complete all those steps manually on hundreds of servers? Well, a lot of time. To automate this, we took advantage of the PowerShell Desired State Configuration, mixed with some custom code. In the end, we produced a set of scripts that helped us to have a two-node cluster with AlwaysOn AG ready in some minutes. Of course it took some time to develop and test it, but now we are ready face another similar challenge and start producing, with minor changes.

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.

With this, I am going to list all the challenges I encountered and how I solved them:

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

Conclusion

[bctt tweet="With automation we can achieve more than purely technical tasks." username="pythian"] With automation we can achieve more than purely technical tasks. As shown, we can take advantage of it and use this time to focus on what matters by leaving the repetitive and easy work to our "personal robot". With a template and a few PowerShell lines, we can automate reports, which will help standardize our work and provide a more in-depth report because there is more time for technical input and analysis. Looking for ways to resolve recurring problems in your business through the strategic use of automation? Pythian can help! Click here to learn more.

On this page