none
add sheets to Excel via VB.net

    Question

  • Hello
    I'm using vb.net and creating an Excel ss. I'm trying to add sheets and name them. So far I have...
    1. Imports Excel = Microsoft.Office.Interop.Excel
    2.  
    3. Public Class Form1
    4.     Private Sub Button1_Click(ByVal sender As System.Object, _
    5.         ByVal e As System.EventArgs) Handles Button1.Click
    6.  
    7.         Dim xlApp As Excel.Application
    8.         Dim xlWorkBook As Excel.Workbook
    9.         Dim xlWorkSheet As Excel.Worksheet
    10.         Dim misValue As Object = System.Reflection.Missing.Value
    11.  
    12.         Dim intCount As Short ' from 0 to 255 - should be sufficient. Integer would add unnecessary "space"
    13.  
    14.         xlApp = New Excel.ApplicationClass
    15.         xlWorkBook = xlApp.Workbooks.Add(misValue)
    16.         xlWorkSheet = xlWorkBook.Sheets("sheet1")
    17.  
    18.         ' add new worksheets & name worksheets ----------------------------
    19.         xlApp.Range("A50:I50").EntireColumn.AutoFit()
    20.  
    21.         With xlWorkBook
    22.             .Sheets("Sheet1").Select()
    23.             '.Sheets("Sheet1").Name = "config"
    24.  
    25.             intCount = 1
    26.  
    27.             'use FOR Instead of While
    28.             'Insert sheet(s) 1 - 7 ( Takoro #1 - Takoro #7 )
    29.             For intCount = 1 To 5
    30.  
    31.                 .Worksheets.Add(After:=.Worksheets(intCount)) 'INSERT AFTER LAST WORKSHEET 
    32.                 'Use counter variable properly to rename all sheets accordingly
    33.                 .Sheets(intCount).Name = "Taroko # " & intCount.ToString()
    34.             Next
    35.  
    36.             .Worksheets.Add(Before:=.Worksheets(1)) 'Add config sheet last. BEFORE Takoro #1
    37.             .Sheets(1).Name = "config" 'Config is now the first sheet
    38.             xlApp.Visible = True
    39.         End With
    40.      End Sub

    a couple of problems, one the data that is supposed to be on the config tab is on Taroko #1 (sheet2) and 2nd there are extra sheets added, sheet 10, 2 and 3 on the furthest to the right.

    thanks for any help.

    Thursday, September 02, 2010 2:06 PM

Answers

  • I ended up using this. Not very elegant but it works...

     

    With xlWorkBook

                .Sheets("Sheet1").Select()

                .Sheets("Sheet1").Name = "config"

                .Sheets("Sheet2").Name = "Taroko #1"

                .Sheets("Sheet3").Name = "Taroko #2"

     

                Dim ws As Excel.Worksheet

                For intCount = 3 To 7

                    'Inserts worksheet after last worksheet

                    ws = .Worksheets.Add(After:=.Worksheets(intCount))

                    'Use counter variable properly to rename all sheets accordingly

                    ws.Name = "Taroko # " & intCount.ToString()

                    ws = Nothing

                Next

    Tuesday, September 07, 2010 12:24 AM

All replies

  • Hello       

    Using vb.net 2008 to create an Excel ss.

    I am trying to add a new sheet and it does but it puts it out of order. It puts it as the first sheet. I need it as the last one ( furthest to the right). Thanks for any help -- Steve

    [code]

      With xlWorkBook

                .Sheets("Sheet1").Select()

                .Sheets("Sheet1").Name = "config"

                .Sheets("Sheet2").Name = "Taroko #1"

                .Sheets("Sheet3").Name = "Taroko #2"

                .Worksheets.Add()

                .Worksheets("Sheet4").Select()

            End With

    [/code]

    • Merged by Liliane Teng Friday, September 03, 2010 10:13 AM same question
    Sunday, August 29, 2010 5:26 PM
  • The second parameter to Add is optional and specifies the object after which the new sheet should be added. So specify the current last sheet and the new one should come after it as in:

                sheet = DirectCast(workbook.Sheets.Add( _
                    After:=workbook.Sheets(workbook.Sheets.Count), _
                    Count:=1, _
                    Type:=Excel.XlSheetType.xlWorksheet), Excel.Worksheet)

    This example demonstrates this:

        http://www.vb-helper.com/howto_net_write_excel.html

    HTH,

    Rod

    Visual Basic 2010 Programmer's Reference
    http://www.vb-helper.com/vb_prog_ref.htm

    Monday, August 30, 2010 1:08 AM
  • I agree with Kaymaf.

     

    By the way kaymaf, get in touch with me.

    Renee

    Monday, August 30, 2010 2:32 AM
  • Rod Stephens, I'm honored I get your news letter.

    Sounds like I need to relocate this post. Not sure how to relocate this.

    Steve

    Monday, August 30, 2010 6:28 PM
  • Rod Stephens, I'm honored I get your news letter.

    Sounds like I need to relocate this post. Not sure how to relocate this.

    Steve

    Dont worry about relocate this thread, the moderators will take care of it. You can try to repost it in the link i gave you in my first reply because it may take a while for moderators to do it and sometimes if moderators does not have an access to particular forum, they tend to move thread to off-topic forum. So it better to repost by yourself.

    kaymaf


    CODE CONVERTER SITE

    http://www.carlosag.net/Tools/CodeTranslator/.

    http://www.developerfusion.com/tools/convert/csharp-to-vb/.

    Tuesday, August 31, 2010 1:00 PM
  • <<a couple of problems, one the data that is supposed to be on the config tab is on Taroko #1 (sheet2) and 2nd there are extra sheets added, sheet 10, 2 and 3 on the furthest to the right.>>

    It would be better to work with exact objects, rather than index values, in order to identify a sheet. Especially here:

    Dim ws as Excel.Worksheet     <br/>For intCount = 1 To 5 
       ws = .Worksheets.Add(After:=.Worksheets(intCount)) 'INSERT AFTER LAST WORKSHEET 
       'Use counter variable properly to rename all sheets accordingly 
       ws.Name = "Taroko # " & intCount.ToString() <br/>   ws = Nothing
    Next 
    
    

    This way, you can be sure you're addressing the correct sheet.

    And use the name to pick up a specific sheet from the colleciton: ws = Worksheets("Taroko 1")


    Cindy Meister, VSTO/Word MVP
    Thursday, September 02, 2010 3:03 PM
    Moderator
  • Hi Cindy

    The code change is giving me the same results

    Thursday, September 02, 2010 7:58 PM
  • Hi Outrider,

    How is this issue going on in your side? Have you solved the problem?

    Best Regards,
    Amy Li
    Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    Monday, September 06, 2010 1:55 AM
    Moderator
  • Hi Cindy

    The code change is giving me the same results


    Please show us the code with the "code change".
    Cindy Meister, VSTO/Word MVP
    Monday, September 06, 2010 6:57 AM
    Moderator
  • Hello

    All my data that is supposed to be in the config sheet is now in sheet1, the order of the sheets are, config, sheet1, Taroko#1, Taroko#2, Taroko#3, Taroko#4, Taroko#5

    Taroko#6, Taroko#7, sheet2, sheet3. Not sure why Sheet1, 2, or 3 is appearing. They are not wanted. I believe sheets are added right to left and not left to right as would

    be expected.

    xlApp = New Excel.ApplicationClass

            xlWorkBook = xlApp.Workbooks.Add(misValue)

            xlWorkSheet = xlWorkBook.Sheets("sheet1")

     

            ' add new worksheets & name worksheets ----------------------------

            xlApp.Range("A50:I50").EntireColumn.AutoFit()

          

            With xlWorkBook

                .Sheets("Sheet1").Select()

                '.Sheets("Sheet1").Name = "config"

     

                intCount = 1

                'Insert sheet(s) 1 - 7 ( Takoro #1 - Takoro #7 )

                'better to work with exact objects, rather than index values, in order to identify a sheet. Especially here:

                Dim ws As Excel.Worksheet

                For intCount = 1 To 7

                    ws = .Worksheets.Add(After:=.Worksheets(intCount)) 'INSERT AFTER LAST WORKSHEET

                    'Use counter variable properly to rename all sheets accordingly

                    ws.Name = "Taroko # " & intCount.ToString()

                    ws = Nothing

                Next

     

                .Worksheets.Add(Before:=.Worksheets(1)) 'Add config sheet last. BEFORE Takoro #1

                .Sheets(1).Name = "config" 'Config is now the first sheet

                xlApp.Visible = True

            End With

    Monday, September 06, 2010 2:15 PM
  • I ended up using this. Not very elegant but it works...

     

    With xlWorkBook

                .Sheets("Sheet1").Select()

                .Sheets("Sheet1").Name = "config"

                .Sheets("Sheet2").Name = "Taroko #1"

                .Sheets("Sheet3").Name = "Taroko #2"

     

                Dim ws As Excel.Worksheet

                For intCount = 3 To 7

                    'Inserts worksheet after last worksheet

                    ws = .Worksheets.Add(After:=.Worksheets(intCount))

                    'Use counter variable properly to rename all sheets accordingly

                    ws.Name = "Taroko # " & intCount.ToString()

                    ws = Nothing

                Next

    Tuesday, September 07, 2010 12:24 AM