Exception automating Excel 2007 with Powershell wh

Question

The following code throws an exception in Powershell V1 (Excel 2007):

$E = New-Object -COM "Excel.Application"
$E.Visible = $True
$wb = $E.Workbooks.Add() #<<<Exception here

The error says that the format might be old or that the type library is not valid (translated from Spanish). A similar script for Word works just fine.

Answer 1

I had this issue when trying to open a workbook. I added this line:

[threading.thread]::CurrentThread.CurrentCulture = 'en-US'

Answer 2

Office interop assemblies seem to have this problem when the current culture is not en-US. The obvious workaround is to set the culture.

It's important to run the whole thing as a single command on the interactive console, since PowerShell V1 always creates a new thread for each command invocation.

PS C:\Users\jachymko> $e = new-object -com excel.application
PS C:\Users\jachymko> $e.workbooks.add()
Exception calling "Add" with "0" argument(s): "Old format or invalid type library. (Exception from HRESULT: 0x80028018 (TYPE_E_INVDATAREAD))"
At line:1 char:17
+ $e.workbooks.add <<<< ()
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : ComMethodTargetInvocation

PS C:\Users\jachymko> & {
>> [threading.thread]::CurrentThread.CurrentCulture = 'en-US'
>> $e = new-object -com excel.application
>> $e.workbooks.add()
>> $e.visible=1
>> }
>>

Answer 3

Adapted to Powershell from one of the solutions proposed in MS Help and Support Article 320369.

$ci = new-object system.globalization.cultureinfo "en-US"

$e = New-Object -COM "Excel.Application"
$e.Visible = $True
$e.UserControl= $True
$books = $e.Workbooks
$books.PSBase.GetType().InvokeMember( `
       "Add", `
       [system.reflection.bindingflags]::InvokeMethod, `
       $null, $books, $null, $ci)

From the same article:

When you use one of these workarounds for a computer where the regional settings do not match the current language version of Office, you should be familiar with how Excel behaves and how Excel will interpret data that might be formatted for a specific locale.