Powershell 1.0 Excel Automation - Problem with "Font.ColorIndex"

Question

I’m trying to automate Excel in Powershell 1.0 and am having problems trying to apply a cells “Font.ColorIndex” property.

The following Microsoft KB article details a BUG with Excel automation when the computer running the script has a locale setting other than “en-us”

My example script below works perfectly when I manually change my locale and regional settings to "en-us" and fails in the last line only when set to "en-gb"

$Excel = New-object -com Excel.Application 
$culture = [System.Globalization.CultureInfo]'en-us'
$Book = $Excel.Workbooks.psbase.gettype().InvokeMember("Add", 
       [Reflection.BindingFlags]::InvokeMethod, 
       $null, $Excel.Workbooks, $null, $culture)
$Sheet = $Book.Worksheets.Item(1)
$Excel.Visible = $True
$Sheet.Cells.Item(1,1).FormulaLocal = "test"
$Sheet.Cells.Item(1,1).Font.ColorIndex = 3

As previously stated, If my locale is set to “en-gb” the script works fine until the last line where it fails with:

Property 'ColorIndex' cannot be found on this object; make sure it exists and is settable. At :line:10 char:29 + $Sheet.Cells.Item(1,1).Font. <<<< ColorIndex = 3

Does anyone have any ideas how to resolve this (other than setting my locale to “en-us” of course!!)

Thanks -Mark

Answer 1

It appears from the KB article that the workarounds all involve setting the culture to en-US unless you want to install the MUI for Office on your PC. The good news is that you can temporarily set the culture to en-US in your script for the problematic code. The following script is something the PowerShell team posted a long time ago but is still handy:

Function Using-Culture (
[System.Globalization.CultureInfo]$culture = `
    (throw "USAGE: Using-Culture -Culture culture -Script {scriptblock}"),
[ScriptBlock]$script= `
    (throw "USAGE: Using-Culture -Culture culture -Script {scriptblock}"))
{
    $OldCulture = [System.Threading.Thread]::CurrentThread.CurrentCulture
    trap 
    {
        [System.Threading.Thread]::CurrentThread.CurrentCulture = $OldCulture
    }
    [System.Threading.Thread]::CurrentThread.CurrentCulture = $culture
    Invoke-Command $script
    [System.Threading.Thread]::CurrentThread.CurrentCulture = $OldCulture
}

Execute the last line like so and see if it works:

Using-Culture en-US { $Sheet.Cells.Item(1,1).Font.ColorIndex = 3 }