I am trying to create an excel work book at run time using vb.net ,then adding multiple
sheets to it (sheet count depends on a variable Value ) and rename the sheets then fill each sheet with data from SQL server table,when running my code an exception thrown, i google it but not solved yet ,the exception "Invalid index. (Exception from HRESULT: 0x8002000B (DISP_E_BADINDEX)) in excel using vb.net"
it occurs at the sentence :xlWorkSheet = xlWorkBook.Sheets(xlWorkBook.Sheets(i).Name())
,any useful thoughts!!
[EDIT]
Dim xlApp As Excel.Application = New Microsoft.Office.Interop.Excel.Application()
If xlApp Is Nothing Then
MessageBox.Show("Excel is not properly installed!!")
Exit Sub
End If
Dim xlWorkBook As New Excel.Workbook
xlWorkBook = xlApp.Workbooks.Add()
Dim xlWorkSheet As New Excel.Worksheet
Dim dtVacations As DataTable
If dtVacations IsNot Nothing AndAlso dtVacations.Rows.Count > 0 Then
xlWorkSheet = CType(xlApp.Worksheets.Add(, , dtVacations.Rows.Count - 1), Excel.Worksheet)
Dim i As Int32 = 1
For Each row In dtVacations.Rows
xlApp.Visible = True
xlWorkSheet = xlWorkBook.Worksheets(i)
xlWorkSheet.Activate()
xlWorkBook.Sheets(i).Name() = NS(row("VacationNameA"))
xlWorkSheet.Cells(1, 1).Value = "EmpNo"
xlWorkSheet.Cells(1, 2).Value = "EmpName"
xlWorkSheet.Cells(1, 3).Value = "HireDate"
xlWorkSheet.Cells(1, 4).Value = "VacNo"
xlWorkSheet.Cells(1, 4).Value = "VacName"
xlWorkSheet.Cells(1, 5).Value = "VacDaysNo"
xlWorkSheet.Cells(1, 6).Value = "BalanceDate"
xlWorkSheet.Cells(1, 7).Value = "DueDate"
xlWorkSheet.Cells(1, 8).Value = "Bal"
xlWorkSheet.Cells(1, 9).Value = "ToDate"
Dim dtEmpVac As DataTable = CreateDataTable(MySql)
If dtEmpVac IsNot Nothing AndAlso dtEmpVac.Rows.Count > 0 Then
For i = 0 To dtEmpVac.Rows.Count - 1
For j = 0 To dtEmpVac.Columns.Count - 1
xlWorkSheet.Cells(i + 3, j + 1) = _
dtEmpVac.Rows(i).Item(j)
Next
Next
End If
i += 1
Next
ElseIf dtVacations IsNot Nothing And dtVacations.Rows.Count = 0 Then
releaseObject(xlWorkBook)
releaseObject(xlApp)
Exit Sub
End If
xlWorkBook.SaveAs(My.Computer.FileSystem.SpecialDirectories.Desktop & "\Vacations.xls", Excel.XlFileFormat.xlWorkbookNormal, _
Excel.XlSaveAsAccessMode.xlExclusive)
xlWorkBook.Close(True)
xlApp.Quit()
releaseObject(xlWorkBook)
releaseObject(xlApp)
The problem occurs after the first loop when go to next sheet!!!
[/EDIT]