Automating excel in powershell

[reverse_atomic_roger]

I'm trying to write a powershell script that will fill in the first cell of a
spreadsheet.

So far I have this...

$excel = new-object -comobject excel.application
$excel.visible = $true

$path = "\\cse2k303\pgreer\WindowsPowerShell\Scripts\test.xls"

#use psbase to open new workbook as excel doesn't like UK regional settings...
$ci = [System.Globalization.CultureInfo]'en-us'
$book = $excel.workbooks.psbase.gettype().InvokeMember("Open",
[Reflection.BindingFlags]::InvokeMethod, $null, $excel.workbooks, $path, $ci)

$sheet = $book.worksheets.item(1)

$sheet.cells.item(1,1).value() = "Testing, testing, 1, 2, 3, 4."


The script seems to work fine until the last line, when powershell gives
this error;

Exception setting "Value": "Exception from HRESULT: 0x800A03EC"
At \\cse2k303\pgreer\WindowsPowerShell\Scripts\Excel2.ps1:19 char:29
+ $sheet.cells.item(2,2).value( <<<< ) = "Testing, testing, 1, 2, 3, 4."


I've tried the last line like this(without the ".value")...

$sheet.cells.item(1,1) = "Testing, testing, 1, 2, 3, 4."

but I get a similar error...

Exception setting "Item": "Exception from HRESULT: 0x800A03EC"
At \\cse2k303\pgreer\WindowsPowerShell\Scripts\Excel2.ps1:19 char:18
+ $sheet.cells.item( <<<< 2,2) = "Testing, testing, 1, 2, 3, 4."

I've searched the 'net for some time, but to no avail. Does anyone have any
suggestions for a fix?

 

[Paul-B]

Heya,

I have had the same issues with regional settings (English Australian)
The information on the net (or TechNet!!) is pretty sparse.

So, after a bit of trawling & trial & error, I have got a solution that
works. This snippet wil help you how to:

1. Add content to cells
2. Save the file
3. Close of Excel

$excel = New-object -com Excel.Application
$ci = [System.Globalization.CultureInfo]'en-us'
$book =
$excel.workbooks.psbase.gettype().InvokeMember("Add",[Reflection.BindingFlags]::InvokeMethod,$null, $excel.workbooks, $null, $ci)
$excel.Visible = $true #Rem this out if you dont want to see the workbook
$worksheet = $book.Worksheets.Item(1)
$worksheet.Cells.item(1,1).formulalocal = "test Cell Data"


$excel.workbooks.psbase.gettype().InvokeMember("SaveAs",[Reflection.BindingFlags]::InvokeMethod,$null, $book, "c:\temp\test.xlsx", $ci) > $null

$excel.workbooks.psbase.gettype().InvokeMember("Close",[Reflection.BindingFlags]::InvokeMethod,$null, $book, 0, $ci) > $null
$excel.Quit()

Hope this helps !!
Cheers