Adding a Sheet to an Excel Workbook

Question

I'm trying to create a Workbook with multiple sheets in Excel but I can't figure out how to create the multiple sheets. I can create one just fine, but when I try to create a second one to write to I get an error.

Dim app As Application = New Application
Dim xlApp As New Excel.Application
Dim xlWorkBook As Excel.Workbook
Dim newXlApp As Excel.Application = New Microsoft.Office.Interop.Excel.Application
Dim newXlWorkbook As Excel.Workbook
Dim newXlSheet As Excel.Worksheet
Dim newXlSheet2 As Excel.Worksheet

Public Sub createWorkBook()
    newXlWorkbook = newXlApp.Workbooks.Add()

    newXlSheet = newXlWorkbook.Sheets("Sheet1")
    newXlSheet2 = newXlWorkbook.Sheets.Add("Sheet2")

    newXlSheet.Cells(1, 1) = "Task ID"
    newXlSheet.Cells(1, 2) = "Collective Tasks"
    newXlSheet.Cells(1, 3) = "Supported Task"

    newXlSheet2.Cells(1, 1) = "Parent Collective Task"
    newXlSheet2.Cells(1, 2) = "Individual Task"
End Sub

I'm not sure if it matters or not, but I also have a separate Excel Workbook open that I'm querying.

Solution

From what I can see the error your code is giving will be:

A first chance exception of type 'System.Runtime.InteropServices.COMException'

If you want to add multiple Sheets to your Excel Workbook this is the code to do that:

Dim app As New Excel.Application
Dim wb As Excel.Workbook = app.Workbooks.Add()
Dim ws As Excel.Worksheet

ws = CType(wb.Sheets.Add(Count:=10), Excel.Worksheet)

By default a Workbook comes with one Sheet. If you want to add more than one set the Count:= parameter. As you can see in my example I have used 10. This will leave me with 11 Sheets to work with.

Note that ws will be the last sheet in the Workbook. In my example this would be Sheet11.

If you want to work with each Worksheet then you would need to look at the following code:

Dim ws1 As Excel.Worksheet = CType(wb.Sheets(1), Excel.Worksheet)
Dim ws2 As Excel.Worksheet = CType(wb.Sheets.Add(), Excel.Worksheet)

ws1.Cells(1, 1) = "Task ID"
ws1.Cells(1, 2) = "Collective Tasks"
ws1.Cells(1, 3) = "Supported Task"

ws2.Cells(1, 1) = "Parent Collective Task"
ws2.Cells(1, 2) = "Individual Task"

Note that ws1 references to the first sheet. As said above a Workbook by default comes with one sheet.

Comments

[SJR]

Which line errors? You only appear to be adding one sheet in your code.

[Bob]

@sjr the newXlSheet2 = newXlWorkbook.Sheets.Add("Sheet2") throws An unhandled exception of type 'System.Runtime.InteropServices.COMException' occurred

[Bugs]

Think the error was A first chance exception of type 'System.Runtime.InteropServices.COMException'. It would be worth including this in the question for anybody to reference if they are having the same error.