Excel with VB.NET (Exception from HRESULT: 0x8002000B (DISP_E_BADINDEX))

6

I'm trying to create an Excel file from VB.net from my first time. I already added the Microsoft.Office.Excel reference, Import the Microsoft.Office.Interop

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

    Dim xlApp As Excel.Application
    Dim xlWorkBook As Excel.Workbook
    Dim xlWorkSheet As Excel.Worksheet
    Dim misValue As Object = System.Reflection.Missing.Value

    xlApp = New Excel.ApplicationClass
    xlWorkBook = xlApp.Workbooks.Add(misValue)
    xlWorkSheet = CType(xlWorkBook.Sheets("sheet1"), Excel.Worksheet)
    xlWorkSheet.Cells(1, 1) = "Something here"
    xlWorkSheet.SaveAs("D:\vbexcel.xlsx")

    xlWorkBook.Close()
    xlApp.Quit()
    releaseObject(xlApp)
    releaseObject(xlWorkBook)
    releaseObject(xlWorkSheet)

    MsgBox("Excel file created , you can find the file c:\")

End Sub

The error generetad is in the line :

   xlWorkSheet = CType(xlWorkBook.Sheets("sheet1"), Excel.Worksheet)

   Invalid index. (Exception from HRESULT: 0x8002000B (DISP_E_BADINDEX))
vb.net
excel
asked on Stack Overflow Feb 2, 2013 by Mouhcine Benmeziane • edited Jan 29, 2014 by Alex

2 Answers

11

Maybe your version of Excel doesn't speak English. And "sheet" is a dirty word in the local language, it kinda is in English ;) Your name is a hint that English is not the default language. Use the index instead of the name to avoid accidents like this:

    xlWorkSheet = CType(xlWorkBook.Sheets(1), Excel.Worksheet)
answered on Stack Overflow Feb 2, 2013 by Hans Passant
2

This could also be happening because

Workbook.Worksheets.Count

is less than the number of sheets you use, depending on how your Excel settings are. For me it starts with 3 sheets but for a colleague it starts with 2 sheets.

So you can call

Workbook.Worksheets.Add()

to reach the desired number of sheets

answered on Stack Overflow Jun 3, 2015 by UrsulRosu

User contributions licensed under CC BY-SA 3.0