How can I create 4th sheet in Excel ?

[TekMem]

Hi,

I am not able to create 4th sheet in Excel. Need help.

Thanks.

 

[PRPhx]

What have you tried? Have you tried using Excel's help? Have you tried to google insert worksheet excel?

 

[TekMem]

Yes I did but I could not figure out.
It should be something
xlSheet = xlBook.Sheets.Add("sheet4")
or
xlSheet = xlBook.Sheets.insert ?

Nothing is working.

 

[djj55]

Try:

xlSheet = xlBook.Worksheets.Add

Then you can change the name of the worksheet.
djj

 

[TekMem]

Dim xname As String = "adb"
        Dim xlApp As Excel.Application
        Dim xlBook As Excel.Workbook
        Dim xlSheet As Excel.Worksheet
xlApp = CType(CreateObject("Excel.Application"), Excel.Application)
        xlBook = CType(xlApp.Workbooks.Add, Excel.Workbook)
        Dim i As Integer = 1
        Do While i < 4

    xlSheet = CType(xlBook.Worksheets(i), Excel.Worksheet)


            xlSheet.Cells(10, 10) = "This is column B row 2"
            xlSheet.Cells(5, 5) = "This is column B row 2"

            xlSheet.Name = "Newsheet" & i

            i = i + 1
        Loop
 xlSheet.SaveAs("C:\Test.xls")

My code works if i is less than 4 , if it is less than 5 gives me error.

 

[TekMem]

Pease help..How can I make my codes work if i < 5.

Thanks.

 

[newora]

I think that your problem is that when you ceate a new workbook, you get 3 worksheets by default. Try going into Excel from the start menu and you will see that if you create a new workbook that it has sheet1,sheet2,sheet3

Thus if i is less than 4 your logic above would work ok, as there are 3 existing worksheets.

Thus I think you may just need to modify you logic to add a worksheet if i is greater than 4 - or maybe clear all worksheets from the workbook collection when you open the workbook and then put a worksheet.add into you loop

HTH

 

[djj55]

Try something like this:

Dim xname As String = "adb"
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
xlApp = CType(CreateObject("Excel.Application"), Excel.Application)
xlBook = CType(xlApp.Workbooks.Add, Excel.Workbook)
Dim i As Integer = 1
Do While i < 5
   Try
      xlSheet = CType(xlBook.Worksheets(i), Excel.Worksheet)
   Catch ex As Exception
      [COLOR=red]xlSheet = xlBook.Worksheets.Add[/color]
      xlSheet = CType(xlBook.Worksheets(i), Excel.Worksheet)
   End Try
   xlSheet.Cells(10, 10) = "This is column B row 2"
   xlSheet.Cells(5, 5) = "This is column B row 2"
   xlSheet.Name = "Newsheet" & i
   i = i + 1
Loop
xlSheet.SaveAs("C:\Test.xls")

djj