Invalid Index. Exception from HRESULT: 0x8002000B (DISP_E_BADINDEX) when Workbooks.Open

1

I am trying to make an app that will open an xlsx file for reading, and than read it and do some stuff with it. When I run my app, and click a button to load the file, I am getting this error:

Invalid Index. Exception from HRESULT: 0x8002000B (DISP_E_BADINDEX)

On this line of code:

Excel.Workbook a
   = excelApp.Workbooks.Open("C:\\test.xlsx", 0, true, 5, "", "", true,
                             Microsoft.Office.Interop.Excel.XlPlatform.xlWindows,
                             "\t", false, false, 0, true, 1, 0);

Can you suggest what could be wrong here?

EDIT: Here is full code so I hope it will be easier to tell what causes the error

 using System;
 using System.Collections.Generic;
 using System.ComponentModel;
 using System.Data;
 using System.Drawing;
 using System.Linq;
 using System.Text;
 using System.Windows.Forms;
 using Excel = Microsoft.Office.Interop.Excel;
 using System.Reflection;


 namespace WindowsFormsApplication2
 {
     public partial class Form1 : Form, IDisposable
     {
         public Form1()
         {
             InitializeComponent();
         }

         private void button1_Click(object sender, EventArgs e)
         {
             Excel.Application excelApp = new Excel.Application();
             excelApp.Visible = true;

             Excel.Workbook a = excelApp.Workbooks.Open("C:/test.xlsx");


        // This selectes the used range of the excel workbook and enters it in
        // a two dimentional array
        try
        {
            // Get a reference to the first sheet of the workbook.
            Excel.Sheets excelSheets = a.Worksheets;
            string currentSheet = "Sheet1";
            Excel.Worksheet excelWorksheet = (Excel.Worksheet)excelSheets.get_Item(currentSheet);

            // write out to console for debugging
            textBox1.Text = "excelWorksheet is " + excelWorksheet;

            // Get a range of data.
            Excel.Range excelCell = (Excel.Range)excelWorksheet.get_Range("A3", Missing.Value);

            // write out to console for debugging
            textBox1.Text = "excelCell is " + excelCell;

            // write out to console for debugging
            textBox1.Text = "Creating string[,] array. . . ";
            // Retrieve the data from the range.
            Object[,] dataArray;
            // write out to console for debugging
            textBox1.Text = "String[,] array created. . . ";

            dataArray = (System.Object[,])excelCell.get_Value(Missing.Value);

            // write out to console for debugging
            textBox1.Text = "Counting rows and columns. . . ";
            // Determine the dimensions of the array.
            int iRows;
            int iCols;
            iRows = dataArray.GetUpperBound(0);
            iCols = dataArray.GetUpperBound(1);

            // write out to console for debugging
            textBox1.Text = "Printing array. . . ";
            // Print the data of the array.
            for (int rowCounter = 1; rowCounter <= iRows; rowCounter++)
            {
                // write out to console for debugging
                textBox1.Text = ("row " + rowCounter);
                for (int colCounter = 1; colCounter <= iCols; colCounter++)
                {

                    // Write the next value to the console.
                    richTextBox1.Text = "col " + colCounter + "= " + dataArray[rowCounter, colCounter].ToString() + ", ";
                }
                // Write in a new line.
                richTextBox1.Text = "\n";

            }
        }
        catch (Exception theException)
        {
            // Create error message
            String errorMessage;
            errorMessage = "Error: ";
            errorMessage = String.Concat(errorMessage, theException.Message);
            errorMessage = String.Concat(errorMessage, " Line: ");
            errorMessage = String.Concat(errorMessage, theException.Source);
            // Display error message
            MessageBox.Show(errorMessage, "Error");
         }

       }
    }
  }
c#
excel
asked on Stack Overflow Mar 24, 2014 by user2886091 • edited Mar 25, 2014 by user2886091

1 Answer

1

I do not know if you have found a solution, but this is a workaround. Hope it helps I was faced with exactly the same issue. the problem is on this line. in my case the workbook has multiple sheets, thus i wanted to loop through each and retrieve data.

string currentSheet = "Sheet1";
        Excel.Worksheet excelWorksheet = (Excel.Worksheet)excelSheets.get_Item(currentSheet);

you are trying to open a sheet with index/name "Sheet1" which does not exist..if you already know the sheet names you are expecting go ahead and use the names otherwise it is best if you use the sheet index. example

Excel.Worksheet excelWorksheet = (Excel.Worksheet)excelSheets.get_Item(1);

this gets the sheet at index 1

this is how i solved mine. i first check if the sheet name exists

var sheetExists = xlWorkBook.Worksheets.Cast<Excel.Worksheet>().FirstOrDefault(worksheet => worksheet.Name == "SomeSheetName"); // this line returns null if the sheet name or index you intend to open does not exist

                        if (sheetExists != null) // this line thus handles the invalid index error. 
                        { /**you can now open the sheet**/
                          Excel.Worksheet excelWorksheet = (Excel.Worksheet)excelSheets.get_Item(currentSheet);
                       }
answered on Stack Overflow Mar 21, 2018 by FRANCISCO KK • edited Mar 21, 2018 by FRANCISCO KK

User contributions licensed under CC BY-SA 3.0