Need to add more than 3 worksheets to excel workbook??

[mike11d11]

I was able to create three worksheets in my workbook, but when I go to
add the 4th I get an Invalid Index error. I must be leaving something
out to when adding 4 or more sheets. Thanks

Dim oExcel As Object
Dim oBook As Object
Dim oSheet1500 As Object
Dim oSheetARPPD As Object
Dim oSheetDFDDNA As Object
Dim oSheetDNE As Object

'Start a new workbook in Excel.
oExcel = CreateObject("Excel.Application")
oBook = oExcel.Workbooks.Add
oSheet1500 = oBook.Worksheets(1)
oSheetARPPD = oBook.Worksheets(2)
oSheetDFDDNA = oBook.Worksheets(3)
oSheetDNE = oBook.Worksheets(4)

'Name teh sheets
oSheet1500.name = "1500"
oSheetARPPD.name = "AR-PPD"
oSheetDFDDNA.name = "DFD-DNA"
oSheetDNE.name = "DNE"
'oSheetEOB.name = "EOB"

 

[Scott M.]

You code doesn't insert any new sheets, it just adds a new workbook to excel
and since workbooks come with 3 sheets already in them, you are only
erroring out when you attempt to refer to sheet 4.

For example, all that this code does is make a variable point to the already
existing sheet 1 in the workbook, not create a new sheet:

oSheet1500 = oBook.Worksheets(1)

You need to declare an object that represents the workbook's worksheets
collection. Then, once you have that you can call the .add method of the
worksheets collection and add new sheets to the collection similarly to how
you added a workbook to the workbooks collection.

 

[mike11d11]

Could you get me started on how to create the collection of worksheets
and then add a fourth sheet. My excel workbook will in the end have
around 20 individual sheets. Sorry I'm farely new to the programming
world and any help would be greatly appreaciated. thanks

 

[Scott M.]

You'll need to make a COM reference to the MS Excel Object Library first.

Sub makeExcelSheet()
Dim xl As New Excel.ApplicationClass
Dim wb As New Excel.WorkbookClass

Dim i As Short
For i = 1 To 4
Dim ws As New Excel.WorksheetClass
wb.Worksheets.Add(ws)
Next
xl.workbooks.add(wb)
End Sub

But I just have to ask....

You are using VB.NET and not VB 6.0, right? This is a newsgroup for VB.NET
questions. Your code that you showed though, looks a little VB 6.0-ish to
me (we don't use "createObject" in .NET).