By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,289 Members | 1,337 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,289 IT Pros & Developers. It's quick & easy.

add sheets to Excel via VB.net

P: 13
Hello
I'm using vb.net and creating an Excel ss. I'm trying to add sheets and name them. So far I have...
Expand|Select|Wrap|Line Numbers
  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
  41.  
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.
Sep 2 '10 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.