Sort-Object problems with Excel Value2

by Scotifer at 2012-11-05 16:26:56

I have the following script (made it as basic as possible):
$Excel = new-object -comobject Excel.Application
$Excel.Visible = $true

$wb = $Excel.Workbooks.Add()
$ws = $wb.Worksheets.Add()
$cells = $ws.Cells
$range = "A1:B1"
$values = @(2, 1)
$cells.Range($range).Value2 = $values <— THIS IS GOOD

$sorted = $values | Sort-Object
$cells.Range($range).Value2 = $sorted # <— THIS FAILS

$sorted[0] = 3
$sorted[1] = 4
$cells.Range($range).Value2 = $sorted <— THIS IS GOOD

$Excel.Quit()
$a = [System.Runtime.InteropServices.Marshal]::ReleaseComObject($Excel)


The first $cells.Range($range).Value2 = $values works, but the second $cells.Range($range).Value2 = $sorted fails. It only fails because of the Sort-Object call. The values appear to no longer be ‘the same type’. The third one works again, after I change the values in the array.

Any ideas, I have spent a day on this.
by poshoholic at 2012-11-05 17:20:44
How does it fail? Do you get an error message?
by Scotifer at 2012-11-06 09:02:27
Sorry, here is the message:
[quote]Exception setting "Value2": "Exception from HRESULT: 0x800A03EC"
At C:\Mine\PowerShell\Epic\test\SortObjectBug.ps1:12 char:1
+ $cells.Range($range).Value2 = $sorted
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:slight_smile: , SetValueInvocationException
+ FullyQualifiedErrorId : CatchFromBaseAdapterSetValueTI[/quote]
by Scotifer at 2012-11-06 09:12:46
One last test, if I do this:
$sorted[0] = [int]$sorted[0]
$sorted[1] = [int]$sorted[1]
$cells.Range($range).Value2 = $sorted

It works. Very odd.
by poshoholic at 2012-11-06 10:00:51
Hmmm. Odd indeed. If that last part works, you should be able to do this as well:
$cells.Range($range).Value2 = [int]$sorted
But what is most puzzling is that inspection of the object types (the array as well as the contents of the array) indicates that, to PowerShell at least, both are the same.
by Scotifer at 2012-11-06 12:51:47
You are correct, casting to [int] also works. On another note, I noticed an error in my code, $range should be:
$range = "A1:B1"