Automating Office and Excel in PowerShell

Tobias — Thu, 19 Aug 2010 05:00:00 GMT

Let's assume you'd like to write results directly to an Excel sheet, or you'd like to dynamically update Excel cells, or read Excel content into your script. In older scripting languages like VBScript, this was easy. Office has COM libraries which allow direct access to all Office products. The same libraries are accessible from PowerShell as well. Here is some sample code that opens Excel, adds a workbook and writes and reads directly to Excel cells:

$excel = New-Object -ComObject Excel.Application
$excel.Visible = $true
$workbook1 = $excel.Workbooks.Add()

# write into cell B5 (column 2, line 5):
$workbook1.ActiveSheet.Cells.Item(5,2)= "I can write directly to cells!"

# read cell content
$content = $workbook1.ActiveSheet.Cells.Item(5,2).Text
"Cell B5 content: $content"

Accessing Office from PowerShell - not that easy!

If that code works for you, happy you! Chances are in the middle of your script, you get an awkward exception, complaining about old formats or invalid type libraries. There has been a lot of confusion just why this error occurs and when. It seems to randomly occur on some machines while others run the code just fine.

Now, the true reason for this is not PowerShell but rather .NET. If your operating system and your MS Office use different languages, you get the exception. Worse yet, if you use MUIs, you may not even notice that there is a language conflict between your OS and your office.

Working around the Language Problem

Fortunately, after some research, we today found a pretty slick workaround. If you have a language conflict, then simply tell PowerShell to execute your code in a different language - and all is fine!

To execute PowerShell in a different language, you need to temporarily change the thread culture. Inspired by an old article Jeffrey Snover posted for Monad, I created an uptodate PowerShell V2 function called Use-Culture. It accepts a culture ID and a script block and then executes the code using the specified culture. Here is the function:

<#
.SYNOPSIS
Executes PowerShell Code using specified culture
.DESCRIPTION
Temporarily switches culture from your default culture to another culture.
.PARAMETER culture
Culture id for the culture you want to use, i.e. en-US for English culture, or de-DE for German culture
.PARAMETER code
PowerShell code you want to run using the specified culture
.EXAMPLE
Use-Culture en-US {Get-Date}
outputs the current date in US format
.EXAMPLE
Use-Culture de-DE {Get-Date}
outputs the current date in German format
.EXAMPLE
Use-Culture ar-IQ {Get-Date}
outputs the current date in Iraq format
.NOTES
Use this command to get a list of all supported culture IDs:
[system.Globalization.CultureInfo]::GetCultures('AllCultures')
.LINK
http://www.powershell.com
#>

function Use-Culture {
param(
[System.Globalization.CultureInfo]
[Parameter(Mandatory=$true)]
$culture,
[ScriptBlock]
[Parameter(Mandatory=$true)]
$code
)

trap {
[System.Threading.Thread]::CurrentThread.CurrentCulture = $currentCulture
}

$currentCulture = [System.Threading.Thread]::CurrentThread.CurrentCulture
[System.Threading.Thread]::CurrentThread.CurrentCulture = $culture
Invoke-Command $code
[System.Threading.Thread]::CurrentThread.CurrentCulture = $currentCulture
}

To see some simple examples, take advantage of the comment based help (after you ran the function code):

Get-Help Use-Culture -Examples

And to get a complete list of all culture IDs you can play with, use this command:

[system.Globalization.CultureInfo]::GetCultures('AllCultures')

Accessing Excel from PowerShell

Now back to Excel. If you get the exception and want to run your code, place your code inside braces and store it in some variable. Then, run Use-Culture with the en-US culture and specify your code like this:

$code = {
$excel = New-Object -ComObject Excel.Application
$excel.Visible = $true
$workbook1 = $excel.Workbooks.Add()

# write into cell B5 (column 2, line 5):
$workbook1.ActiveSheet.Cells.Item(5,2)= "I can write directly to cells!"

# read cell content
$content = $workbook1.ActiveSheet.Cells.Item(5,2).Text
"Cell B5 content: $content"
}

Use-Culture en-US $code

It works! Finally.

Likewise, if you normally do not get the weird Excel exception, simply specify a conflicting culture such as ar-IQ and watch the exception pop up.

Enjoy and have fun!

Tobias
Microsoft MVP PowerShell

Search results for 'app:weblogs' matching tags 'Office' and 'Use-Culture'

Automating Office and Excel in PowerShell

Accessing Office from PowerShell - not that easy!

Working around the Language Problem

Accessing Excel from PowerShell