Writing data to cells in Excel 2007 / PowerShell

Question

Hello,

Why can I not write values to Excel using the Worksheet class, or Sheet interface? I would expect to be able to do something like this:

[Reflection.Assembly]::LoadWithPartialName("Microsoft.Office.Interop.Excel")
$Excel = New-Object Microsoft.Office.Interop.Excel.ApplicationClass
$Workbook = $Excel.Workbooks.Add()
$Worksheet = $Workbook.Worksheets.Add()
$Worksheet.Cells.Item(1,1).Value2 = "Test"

But instead, it seems that you have to write values using the ApplicationClass object:

[Reflection.Assembly]::LoadWithPartialName("Microsoft.Office.Interop.Excel")
$Excel = New-Object Microsoft.Office.Interop.Excel.ApplicationClass
$Workbook = $Excel.Workbooks.Add()
$Worksheet = $Workbook.Worksheets.Add()
$Excel.Cells.Item(1,1).Value2 = "Test"

This doesn't seem logical to me, because I'm writing the value to the specific worksheet I'm working with, not at the application level.

Any thoughts on this?

Answer 1

I cannot reproduce this. Your former example works perfectly for me. I verified by setting $Excel.Visible = $true and I can see "Test" in cell(1,1).

-Oisin