how to add multiple sheets to excel file

1

i am adding multiple sheets to excel but it shows the exception System.Runtime.InteropServices.COMException: 'Invalid index. (Exception from HRESULT: 0x8002000B (DISP_E_BADINDEX))' . the exception occurs at Position: xlWorkBook.Sheets("105")

    Dim xlApp As Excel.Application
    Dim xlWorkBook As Excel.Workbook
    Dim xlWorkSheet105 As Excel.Worksheet
    Dim xlWorkSheet120 As Excel.Worksheet
    Dim xlWorkSheet125 As Excel.Worksheet
    Dim xlWorkSheet600 As Excel.Worksheet
    Dim misValue As Object = System.Reflection.Missing.Value

    xlApp = New Excel.Application
    xlWorkBook = xlApp.Workbooks.Add()
    xlWorkSheet105 = xlWorkBook.Sheets("105")
    xlWorkSheet120 = xlWorkBook.Sheets("120")
    xlWorkSheet125 = xlWorkBook.Sheets("125")
    xlWorkSheet600 = xlWorkBook.Sheets("600")
excel
vb.net
worksheet
asked on Stack Overflow Jan 13, 2020 by ilyas

1 Answer

0
    Dim xlApp As Excel.Application
    Dim xlWorkBook As Excel.Workbook
    Dim xlWorkSheetHeader As Excel.Worksheet
    Dim xlWorkSheet105 As Excel.Worksheet
    Dim xlWorkSheet120 As Excel.Worksheet
    Dim xlWorkSheet125 As Excel.Worksheet
    Dim xlWorkSheet600 As Excel.Worksheet
    Dim xlWorkSheetFooter As Excel.Worksheet
    Dim misValue As Object = System.Reflection.Missing.Value

    xlApp = New Excel.Application
    xlWorkBook = xlApp.Workbooks.Add()
    xlWorkSheetHeader = xlWorkBook.Sheets.Add(xlWorkBook.Worksheets(xlWorkBook.Worksheets.Count))
    xlWorkSheetHeader.Name = "Header"
    xlWorkSheet105 = xlWorkBook.Sheets.Add(xlWorkBook.Worksheets(xlWorkBook.Worksheets.Count))
    xlWorkSheet105.Name = "105"
    xlWorkSheet120 = xlWorkBook.Sheets.Add(xlWorkBook.Worksheets(xlWorkBook.Worksheets.Count))
    xlWorkSheet120.Name = "120"
    xlWorkSheet125 = xlWorkBook.Sheets.Add(xlWorkBook.Worksheets(xlWorkBook.Worksheets.Count))
    xlWorkSheet125.Name = "125"
    xlWorkSheet600 = xlWorkBook.Sheets.Add(xlWorkBook.Worksheets(xlWorkBook.Worksheets.Count))
    xlWorkSheet600.Name = "600"
    xlWorkSheetFooter = xlWorkBook.Sheets.Add(xlWorkBook.Worksheets(xlWorkBook.Worksheets.Count))
    xlWorkSheetFooter.Name = "Footer"          xlWorkBook.SaveAs(Path)

This code work for me. In last I save the xlWorkBook

answered on Stack Overflow Jan 13, 2020 by ilyas • edited Jan 13, 2020 by ilyas

User contributions licensed under CC BY-SA 3.0