PowerShell.nu

I got this idea from “dmtelf” who posted a comment on a previous post I wrote regarding Microsoft Word and PowerShell. I ran through a few tests and this is the result.

First, lets Insert a Page Break after every Paragraph in a Word Document. The script will insert a Page break after each Paragraph. I’ve created a sample Word Document that has 5 Lorem Ipsum paragraphs.

Connecting to the Word Document is done through the Word.Application -Com Object. Then we use the Open() method to connect to our Word Document. Next we create a New Variable holding every Paragraph in the Word Document, Loop through the Paragraphs and set the PageBreak to -1. This sets a Pagebreak for each new Paragraph.

function Insert-PageBreak ([string]$File) {
	$Word = New-Object -Com Word.Application
	$OpenDoc = $Word.Documents.Open($File)
	$Paragraphs = $OpenDoc.Paragraphs

	$Paragraphs | ForEach {

		$_.PageBreakBefore = -1

	}

	$OpenDoc.Close()
}

Running the Function on a Word Document inserts a PageBreak after each Paragraph as shown below:

PS > Insert-PageBreak "C:\Documents\Lorem Ipsum.doc"

Now, what if i want to insert a Pagebreak after a “Heading 1″ instead of a paragraph, when I write Word documents i sometimes get a little sloppy and push “enter” a little too often so that i get lots of unwanted paragraphs.

We can solve this with a simple if statement as shown below.

function Insert-PageBreak ([string]$File) {
	$Word = New-Object -Com Word.Application
	$OpenDoc = $Word.Documents.Open($File)
	$Paragraphs = $OpenDoc.Paragraphs

	$Paragraphs | ForEach {

	$_.Style.NameLocal
		if ($_.Style.NameLocal -match "Heading 1") {
			$_.PageBreakBefore = -1
		}
	}

	$OpenDoc.Close()
}

Running the Function on a Word Document containing “Heading 1″ sets the PageBreak for each New “Heading 1″

PS > Insert-PageBreak "C:\Documents\Lorem Ipsum.doc"

Note that if the Headings are not correctly inserted in the Word Document, it might screw things up a little in the Document, so take a backup before trying this at home

Click Here to download a Script with a little more functionality

In a previous migration project, we had some issues with Word documents, actually a couple of thousand Word documents containing Server names pointing to the old servers.. In other words, after the migration, all these word documents would point to the wrong server. Changing this manually would take weeks. Changing the documents through PowerShell would take a few minutes.

In this post, I’m going to demonstrate how you can manipulate text in Word documents through PowerShell. Let’s say we have a document containing the Lyrics to Johhny Cash – Ring of Fire.

Lets replace the word “ring of fire” with “Chuck Norris RoundHouse Kick”

The function used is pretty straight forward. First setting up the variables required by the Execute() method and then connecting to the document that we want to manipulate.

function Replace-Word ([string]$Document,[string]$FindText,[string]$ReplaceText) {

	#Variables used to Match And Replace

	$ReplaceAll = 2
	$FindContinue = 1

	$MatchCase = $False
	$MatchWholeWord = $True
	$MatchWildcards = $False
	$MatchSoundsLike = $False
	$MatchAllWordForms = $False
	$Forward = $True
	$Wrap = $FindContinue
	$Format = $False

	$Word = New-Object -comobject Word.Application
	$Word.Visible = $False

	$OpenDoc = $Word.Documents.Open($Document)
	$Selection = $Word.Selection

	$Selection.Find.Execute(
		$FindText,
		$MatchCase,
		$MatchWholeWord,
		$MatchWildcards,
		$MatchSoundsLike,
		$MatchAllWordForms,
		$Forward,
		$Wrap,
		$Format,
		$ReplaceText,
		$ReplaceAll
	)

	$OpenDoc.Close()
}

Running the function would look something like this:

Replace-Word C:\Ring Of Fire.docx "ring of fire" "Chuck Norris RoundHouse Kick"

Here’s what happend to the Document when I ran the function.

Click here to download the Script.

A friend at my company just asked me how to modify a Excel Spredsheet based on a template and save it as a new Excel spredsheet through PowerShell. Looking inot this, There where some issues when trying to close the Excel file through PowerShell. After a little research I found an article by Hristo Deshev that was really helpful on this. Anyway, let’s start by looking at the template file. The template file in this example is a simplified version of the one we used but the PowerShell commands are basically the same.

So it’s basically 2 cells that I’m going to modify and then saving it into a new Excel File.

Connecting to Excel through PowerShell is done using the Excel.Application COM Object.

PS > $Excel = New-Object -Com Excel.Application

Now that we have a connection to Excel we can open our template file through the Open() method. Use a fully qualified path when pointing out the excel template.

PS > $WorkBook = $Excel.Workbooks.Open(C:\Excel\Template.xls)

Next, we have to point out which sheet we want o edit. This example uses the first sheet so if you want to modify another sheet, just change the number.

PS > $WorkSheet = $WorkBook.Worksheets.Item(1)

Now that we have a connection to the Sheet, we can start Adding or Modifying information. The Cells that I want to change in this example are A2 and B2. I can specify this through the Item() method. The first Value is Row and the second is Column. When this is done, we can set the Value2 property.

PS > $AddFirstName = $WorkSheet.Cells.Item(2,1)
PS > $AddFirstName.Value2 = $FirstName

PS > $AddLastName = $WorkSheet.Cells.Item(2,2)
PS > $AddLastName.Value2 = $LastName

Great. The last step is cleaning up and saving into a new Excel file. It’s important that we Null all Variables that are connected to Excel, otherwise the Excel SpredSheet won’t close correctly. First we Null the $Add variables.

PS > $AddFirstName = $Null
PS > $AddLastName = $Null

Next, we save the Information to a new Excel Spredsheet and close the Excel connection

PS > $WorkBook.SaveAs(C:\Excel\MyNewFile.xls)
PS > $Excel.Quit()

Last step is Nulling all remaining variables connected to Excel and finally releasing the Object Wrapper. This is done by calling [GC]::Collect()

PS > $WorkBook = $Null
PS > $WorkSheet = $Null
PS > $Excel = $Null
PS > [GC]::Collect()

And now we have a new Excel Spredsheet based on a template file.

Below is a function that automates these steps. Just Change the $File Variable to your Template file instead.

function Set-Excel ($ExcelFile, $FirstName, $LastName) {

	# Template File. Set-Location to the
	# Folder where the template file is placed.

	$File = (ls Template.xls).FullName

	# Open Excel

	$Excel = New-Object -Com Excel.Application

	# Open Template File 

	$WorkBook = $Excel.Workbooks.Open($File)
	$WorkSheet = $WorkBook.Worksheets.Item(1)

	$AddFirstName = $WorkSheet.Cells.Item(2,1)
	$AddFirstName.Value2 = $FirstName

	$AddLastName = $WorkSheet.Cells.Item(2,2)
	$AddLastName.Value2 = $LastName

	# Setting All Variables to Null

	$AddFirstName = $Null
	$AddLastName = $Null

	$WorkBook.SaveAs($ExcelFile)
	$Excel.Quit()

	$WorkBook = $Null
	$WorkSheet = $Null
	$Excel = $Null

	# Releasing Object Wrapper

	[GC]::Collect()

}

Examples on running the function:

PS > Set-Excel C:\Excel\MyNewFile.xls Niklas Goude