by Scotifer at 2012-11-05 16:26:56
I have the following script (made it as basic as possible):by poshoholic at 2012-11-05 17:20:44$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 theSort-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.
How does it fail? Do you get an error message?by Scotifer at 2012-11-06 09:02:27
Sorry, here is the message:by Scotifer at 2012-11-06 09:12:46
[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: ( , SetValueInvocationException
+ FullyQualifiedErrorId : CatchFromBaseAdapterSetValueTI[/quote]
One last test, if I do this:by poshoholic at 2012-11-06 10:00:51$sorted[0] = [int]$sorted[0]
$sorted[1] = [int]$sorted[1]
$cells.Range($range).Value2 = $sorted
It works. Very odd.
Hmmm. Odd indeed. If that last part works, you should be able to do this as well:by Scotifer at 2012-11-06 12:51:47$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.
You are correct, casting to [int] also works. On another note, I noticed an error in my code, $range should be:$range = "A1:B1"