Excel worksheet get item

Question

I have a problem with Excel worksheet. I am trying to create an Excel file with c#.

This code works and runs correctly on my computer but in other computers get an error at last line:

Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheetInvoice;
Excel.Worksheet xlWorkSheetInvoiceLine;

object misValue = System.Reflection.Missing.Value;

xlApp = new Microsoft.Office.Interop.Excel.Application();
xlWorkBook = xlApp.Workbooks.Add(misValue);

xlWorkSheetInvoice = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

xlWorkSheetInvoiceLine = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(2);

System.Runtime.InteropServices.COMException (0x8002000B): Invalid index. (Exception from HRESULT: 0x8002000B (DISP_E_BADINDEX)) at Microsoft.Office.Interop.Excel.Sheets.get_Item(Object Index)

score 1 · Answer 1 · answered Feb 18 '14 at 09:56

I executed a sample application with similar code on a machine with Excel 2013 and it fails at the same line of code you mentioned. By default Excel 2013 application opens up with a single worksheet ("Sheet1") so you would have to modify the code accordingly

score 0 · Answer 2 · answered Feb 18 '14 at 08:15

0

DISP_E_BADINDEX seems to suggest the number of worksheets is less on the other computers. Build in a check to see if the number of worksheets is less than 2 before using get_Item().

answered Feb 18 '14 at 08:15

David

853
8
24

score 0 · Answer 3 · answered Feb 18 '14 at 11:31

i have created new sheet and assigned xlWorkSheetInvoice and xlWorkSheetInvoiceLine to solve it.

var xlSheets = xlWorkBook.Sheets as Excel.Sheets;
            var xlNewSheet = (Excel.Worksheet)xlSheets.Add(xlSheets[1], Type.Missing, Type.Missing, Type.Missing);
            var xlNewSheet2= (Excel.Worksheet)xlSheets.Add(xlSheets[2], Type.Missing, Type.Missing, Type.Missing);
            xlWorkSheetInvoice = xlNewSheet;
            xlWorkSheetInvoiceLine = xlNewSheet2;
            xlWorkSheetInvoice = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
            xlWorkSheetInvoiceLine = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(2);

Excel worksheet get item

3 Answers3