PowerShell to Excel Cell error

[jnord]

New to PowerShell and looking for anyone that might know if this should work or if I have some other issue going on; I get all the data to write but the vmhost data does populate the date like I want. My code is below with the error.

$Excel = New-Object -comobject Excel.Application

$Excel.visible = $True

$Excel = $Excel.Workbooks.Add(1)

$Sheet = $Excel.WorkSheets.Item(1)

$Sheet.Cells.Item(1,1) = “Hosts”

$Sheet.Cells.Item(1,2) = “VM”

$Sheet.Cells.Item(1,3) = “Clusters”

$hostcount=Get-VMHost | Measure-Object |select count

$Sheet.Cells.Item($intRow,1) = "$hostcount"

Exception setting "Item": "Exception from HRESULT: 0x800A03EC"

At D:\powershell\workinpro.ps1:19 char:1

+ $Sheet.Cells.Item($intRow,1) = "$hostcount"

+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

  + CategoryInfo          : NotSpecified: (:) [], SetValueInvocationException

+ FullyQualifiedErrorId : CatchFromBaseAdapterParameterizedPropertySetValueTI

Thanks For any help you can give new guy on the block

[LucD]

Try it like this

$Excel = New-Object -comobject Excel.Application 
$Excel.visible = $True 
$Excel = $Excel.Workbooks.Add(1)
$Sheet = $Excel.WorkSheets.Item(1)
$Sheet.Cells.Item(1,1) = “Hosts” 
$Sheet.Cells.Item(1,2) = “VM” 
$Sheet.Cells.Item(1,3) = “Clusters” 
$hostcount=Get-VMHost | Measure-Object |select -ExpandProperty Count
$intRow = 2
$Sheet.Cells.Item($intRow,1).Value2 = $hostcount

[jnord]

The code is working I just had to add the .count with the changes you already made for me. Thanks for your help this got me going on the right track.

$Sheet.Cells.Item($intRow,1).Value2 = $hostcount.count

Now I have to go understand what going on so I can write it with out copying it.

[LucD]

You shouldn't have to use $hostcount.Count when you use the ExpandProperty parameter on the Select cmdlet.

Then just $hostcount should do.

[jnord]

Hmm If i do not add that I was still geting the HRESULT: 0x800A03EC error. 

[LucD]

And you did change line 8 as well (as in my code above) ?

[jnord]

I think so this is the what i have have set for the code.

<p>$Excel = New-Object -comobject Excel.Application<p>
<p>$Excel.visible = $True<p>
<p>$Excel = $Excel.Workbooks.Add(1)<p>
<p>$Sheet = $Excel.WorkSheets.Item(1)<p>
<p>$Sheet.Cells.Item(1,1) = “Hosts”<p>
<p>$Sheet.Cells.Item(1,2) = “VM”<p>
<p>$Sheet.Cells.Item(1,3) = “Clusters”<p>

<p>$hostcount=Get-VMHost | Measure-Object |select -ExcludeProperty count<p>
<p>$intRow=2<p>
<p>$Sheet.Cells.Item($intRow,1).Value2 = $hostcount.Count<p>

[LucD · Accepted solution]

That seems to say ExcludeProperty, and not ExpandProperty.

The disadvantage of Intelisense

[jnord]

Yep that was it thanks for picking that out for me. i hope this will just flow with me one day.