Udi Goldstein

Udi Goldstein

  • NA
  • 22
  • 63.6k

COM exception while selecting cells in excel with c#

Jul 26 2011 1:10 PM
I have a problem selecting a cell in excel using c# while running through a "for" loop.

I have 3 datatables in a dataset(dsReportGrid) which I'm exporting to excel (one sheet per table).
When I try to select a range so I can freeze panes, it goes well in the first iteration of the loop (dsReportGrid.table[0]), but when it gets to second iteration when I try to select the cell in the secondsheet it throws me a COM exception on the select event (the code line with two asterisks).

Why is it happening and how can I handle it?

Thanks,

        private void saveXlS()
        {
            Excel.Application xlApp;
            Excel.Workbook xlWorkBook;
            Excel.Worksheet xlWorkSheet;
            Excel.Range xlCells;
            Excel.Range freezeCell;
            object misValue = System.Reflection.Missing.Value;

            xlApp = new Excel.ApplicationClass();
            xlWorkBook = xlApp.Workbooks.Add(misValue);

            for (int table = 0; table <= dsReportGrid.Tables.Count -1; table++)
            {

                xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(table +1);
                   Excel.Range k = xlWorkSheet.get_Range("a2");
                   **k.Select();**
                   k.Application.ActiveWindow.FreezePanes = true;
                   break;
            }
            ...rest of code writing from dataset to excel
       }
                 

Answers (3)