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?
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