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:
$Word=NEW-Object –comobject Word.Application
With the document opened, I could perform all of the necessary changes, save the file and use the following function:
Function SaveAsWordDoc($Document, $FileName)
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)
$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)
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.
Thanks for the tutorial.
I have some trouble when copy pasting this code in VS Code. The font/typography causes some issues and when calling the Function ReplaceTag, the fact that not all parameters are capitalised exactly as written in the ReplaceTag function may cause issues. It seems Powershell ISE is less picky.
I also suggest to end the code with $Word.Quit() to avoid many instances of ‘ghost’ MS Word when using this script to mass produce Word documents. Please note that this requires changing something to the Function OpenWordDoc: $script:Word = New-Object -comobject Word.Application (the change at the start to $script:Word does the trick, making the $Word object available outside the Function).
Keep up the good work
Murilo Miranda, thanks for sharing very useful information. I have a a requirement where i want to perform Break link in a document and save the file as Docx using Powershell. is it possible. “Break link is some thing that when you want to remove all external link to picture in document.
cracking method for some easy template word doc generation.