Powershell and Excel

[Arto Viitanen]

There is a thread on using Excel with PowerShell, but it does not work
for me. I tried to run the
http://www.microsoft.com/technet/scriptcenter/resources/qanda/sept06/hey0908.mspx
example, but it did not work. Based on suggestions to the thread, I
tried following:

$thisThread = [System.Threading.Thread]::CurrentThread
$originalCulture = $thisThread.CurrentCulture
$thisThread.CurrentCulture = New-Object
System.Globalization.CultureInfo('en-US')
$a = New-Object -comobject Excel.Application
$a.Visible = $True
$b = $a.Workbooks.Add()
$c = $b.Worksheets.Item(1)
$c.Cells.Item(1,1) = "A Value in cell A1."
$b.SaveAs("C:\Temp\Test.xls")
$a.Quit()
$thisThread.CurrentCulture = $originalCulture

(If I use get-date instead of lines 4-11 and add get-date after
the script, it seems the CultureInfo is changed to en-US, but still
I get errors:

PS C:\Documents and Settings\aviitane> ./Excel.ps1
Exception calling "Add" with "0" argument(s): "Old format or invalid
type library. (Exception from HRESULT: 0x80028018
(TYPE_E_INVDATAREAD))"
At C:\Documents and Settings\aviitane\Excel.ps1:6 char:22
+ $b = $a.Workbooks.Add( <<<< )
You cannot call a method on a null-valued expression.
At C:\Documents and Settings\aviitane\Excel.ps1:7 char:24
+ $c = $b.Worksheets.Item( <<<< 1)
You cannot call a method on a null-valued expression.
At C:\Documents and Settings\aviitane\Excel.ps1:8 char:14
+ $c.Cells.Item( <<<< 1,1) = "A Value in cell A1."
You cannot call a method on a null-valued expression.
At C:\Documents and Settings\aviitane\Excel.ps1:9 char:10
+ $b.SaveAs( <<<< "C:\Temp\Test.xls")

So is the example too old (I use Excel 2003) or what is the problem?

--

Arto Viitanen, CSC Ltd
Espoo, Finland

[Roman Kuzmin]

I suppose "You run an English version of Excel but the regional settings for
the computer are configured for a non-English language." If so, look at
http://support.microsoft.com/default.aspx?scid=kb;en-us;320369

Some example PS code using workaround (opens a file and saves in different
format):

[code]

$excel = New-Object -COM Excel.Application
$ci = [System.Globalization.CultureInfo]'en-US'
$book = $excel.Workbooks.PSBase.GetType().InvokeMember(
'Open', [Reflection.BindingFlags]::InvokeMethod, $null,
$excel.Workbooks, $src, $ci)
[void]$book.PSBase.GetType().InvokeMember(
'SaveAs', [Reflection.BindingFlags]::InvokeMethod, $null, $book, ($res,
$fmt), $ci)
[void]$book.PSBase.GetType().InvokeMember(
'Close', [Reflection.BindingFlags]::InvokeMethod, $null, $book, 0, $ci)

[/code]

--
Thanks,
Roman

[Roman Kuzmin]

Perhaps, the better workaround is:

[CODE]

# US culture info
$ciUS = [System.Globalization.CultureInfo]'en-US'

# invoke an object's method using the culture info
function Invoke([object]$m, [string]$method, $parameters)
{
$m.PSBase.GetType().InvokeMember(
$method, [Reflection.BindingFlags]::InvokeMethod, $null, $m, $parameters,
$ciUS)
}

[/CODE]

Now call Invoke instead of Excel methods that require culture info. My
previous example now looks much better:

[EXAMPLE]

param($InputPath, $OutputPath, $Format)

$excel = New-Object -COM Excel.Application

$book = Invoke $excel.Workbooks Open $InputPath
Invoke $book SaveAs ($OutputPath, $Format) >$null
Invoke $book Close 0 >$null

[/EXAMPLE]

--
Thanks,
Roman

[Arto Viitanen]

I got your example working, but still cannot add new data to the sheet.
I guess the problem is I do not know how to use properties via the dispatch.

--
Arto Viitanen, CSC Ltd.
Espoo, Finland

[Arto Viitanen]

Arto Viitanen wrote:

> I got your example working, but still cannot add new data to the sheet.
> I guess the problem is I do not know how to use properties via the
> dispatch.
>

I tried also the Export-Csv, but it has problems also. As from my
problems shows, I use Excel with Finnish localization. Export-Csv
generates values with comma separation, but if there is float value,
it puts hyphens around it. For example row of values

corona 6 99.5497685416667

is stored as corona,6,"99,5497685416667"

If I try to read this on Excel using Finnish localization, it does not
work since in Finland ; is used as value separation. If I try to read
this using English localization, I does not get 99.5497685416667,
since it is stored as text.

So is this bug, or how can I tell Export-Csv to use current
localization?

[Roman Kuzmin]

There is a suggestion on Connect:
"import-csv and export-csv should allow specification of an alternate
separator character"
https://connect.microsoft.com/feedback/ViewFeedback.aspx?FeedbackID=152145&SiteID=99
You can vote for it, too. As far as I can see the issue is "postponed",
hopefully it will come in a future version of PowerShell.

As for using properties in Excel, I guess you may use the same technique
with $object.PSBase.GetType().InvokeMember() used with
[Reflection.BindingFlags]::GetProperty and
[Reflection.BindingFlags]::SetProperty instead of
[Reflection.BindingFlags]::InvokeMethod (I did not try this way, so I have
no examples yet). I also know that many properties and methods do not
require this workaround, here is a working code with some properties/methods
used directly:

[CODE]

for($i = $book.Worksheets.Count; $i -gt 0; --$i) {
$ws = $book.Worksheets.Item($i)
for($j = $ws.QueryTables.Count; $j -gt 0; --$j)
{
$qt = $ws.QueryTables.Item($j)
$qt.BackgroundQuery = 0
}
}

[/CODE]

--
Thanks,
Roman

[Arto Viitanen]

Roman Kuzmin wrote:
> As for using properties in Excel, I guess you may use the same technique
> with $object.PSBase.GetType().InvokeMember() used with
> [Reflection.BindingFlags]::GetProperty and
> [Reflection.BindingFlags]::SetProperty instead of

Thanks, I finally make it (saving an SQL Server query to Excel file)
work, using SetProperty. OK, I have problems with GetProperty, but
it is not necessary.