7

Why does this work:

((Excel.Worksheet)Application.ActiveSheet).get_Range("A1", "A1").Value2 = text;

But this doesn't:

Excel.Worksheet activeSheet = ((Excel.Worksheet)Application.ActiveSheet);
activeSheet.Cells[0, 0] = text;

I need to do it the second way as I need to loop with rowIndex and colIndex. How can I do that?

I get the error:

An unhandled exception of type 'System.Runtime.InteropServices.COMException' occurred in mscorlib.dll Additional information: Exception from HRESULT: 0x800A03EC

JP Alioto
  • 44,864
  • 6
  • 88
  • 112

1 Answers1

14

The only thing you're missing (other than using 1 based indexes) is to cast the cell reference to a range explicitly:

Excel.Worksheet activeSheet = ((Excel.Worksheet)Application.ActiveSheet);
int endRow = 5;
int endCol = 6;

for(int idxRow = 1; idxRow <= endRow; idxRow++)
{
    for(int idxCol = 1; idxCol <= endCol; idxCol)
    {
        ((Excel.Range)activeSheet.Cells[idxRow, idxCol]).Value2 = "Kilroy wuz here";
    }
}
Paul Keister
  • 12,851
  • 5
  • 46
  • 75
  • 2
    +1 I just wanted to make people who read this know the faster way to populate a large amount of cell values is using a object[,] eg: http://stackoverflow.com/a/3847094/495455 – Jeremy Thompson Nov 14 '12 at 23:23
  • With c# 4 and the 'dynamic' keyword one can now just: dynamic cell = sheet.Cells[1, 1]; cell.Value2 = "hi there"; More - http://msdn.microsoft.com/en-us/magazine/gg598922.aspx –  Aug 28 '13 at 07:11