PowerShell.nu

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