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))
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)
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
User contributions licensed under CC BY-SA 3.0