How do I create a sheet in Excel from VB.net?

  • Thread starter Thread starter KC
  • Start date Start date
K

KC

Does anybody know how to add a specific number of worksheets to an Excel
spreadsheet through VB.net? I'm trying to export some datatables to an excel
file, but I only want as many sheets in the workbook as there are tables.

Right now the routine I'm tweaking from

http://support.microsoft.com/default.aspx?scid=kb;EN-US;306022

adds the default, (3).

At this stage I can export data fine, I just don't know how to control the
number sheets.
 
Hi,

Dim oExcel As Microsoft.Office.Interop.Excel.Application

Dim oBook, oBook1 As Microsoft.Office.Interop.Excel.Workbook

Dim oSheet As Microsoft.Office.Interop.Excel.Worksheet

'Start a new workbook in Excel.

oExcel = New Microsoft.Office.Interop.Excel.Application

oBook = oExcel.Workbooks.Add

oBook1 = oExcel.Workbooks.Add

'Add data to cells of the first worksheet in the new workbook.

oSheet = CType(oBook.Worksheets(1),
Microsoft.Office.Interop.Excel.Worksheet)

oSheet.Range("A1").Value = "Last Name"

oSheet.Range("B1").Value = "First Name"

oSheet.Range("C1").Value = "Price"

oSheet.Range("A1:B1").Font.Bold = True

oSheet.Range("A2").Value = "Doe"

oSheet.Range("B2").Value = "John"

oSheet.Range("C2").Value = 12345.456

oSheet.Range("C2").Cells.NumberFormat = "$0.00"

oSheet = CType(oBook.Worksheets(2),
Microsoft.Office.Interop.Excel.Worksheet)

oSheet.Range("A1").Value = "Last Name"

oSheet.Range("B1").Value = "First Name"

oSheet.Range("C1").Value = "Price"

oSheet.Range("A1:B1").Font.Bold = True

oSheet.Range("A2").Value = "Doe"

oSheet.Range("B2").Value = "John"

oSheet.Range("C2").Value = 12345.456

oSheet.Range("C2").Cells.NumberFormat = "$0.00"

'Save the Workbook and quit Excel.

oExcel.DisplayAlerts = False

oBook.SaveAs("c:\Book1.xls")

oSheet = Nothing

oBook = Nothing

oExcel.Quit()

oExcel = Nothing

GC.Collect()



Ken

-------------------------------------

Does anybody know how to add a specific number of worksheets to an Excel
spreadsheet through VB.net? I'm trying to export some datatables to an excel
file, but I only want as many sheets in the workbook as there are tables.

Right now the routine I'm tweaking from

http://support.microsoft.com/default.aspx?scid=kb;EN-US;306022

adds the default, (3).

At this stage I can export data fine, I just don't know how to control the
number sheets.
 
http://www.kjmsolutions.com/datasetarray.htm



________________________________

From: KC [mailto:[email protected]]
Sent: Wednesday, August 25, 2004 4:55 PM
To: microsoft.public.dotnet.languages.vb
Subject: How do I create a sheet in Excel from VB.net?



Does anybody know how to add a specific number of worksheets to an Excel
spreadsheet through VB.net? I'm trying to export some datatables to an
excel
file, but I only want as many sheets in the workbook as there are
tables.

Right now the routine I'm tweaking from

http://support.microsoft.com/default.aspx?scid=kb;EN-US;306022

adds the default, (3).

At this stage I can export data fine, I just don't know how to control
the
number sheets.
 
¤ Does anybody know how to add a specific number of worksheets to an Excel
¤ spreadsheet through VB.net? I'm trying to export some datatables to an excel
¤ file, but I only want as many sheets in the workbook as there are tables.
¤
¤ Right now the routine I'm tweaking from
¤
¤ http://support.microsoft.com/default.aspx?scid=kb;EN-US;306022
¤
¤ adds the default, (3).
¤
¤ At this stage I can export data fine, I just don't know how to control the
¤ number sheets.

What is the data source from which you are exporting to Excel? Are you exporting directly from .NET
DataTables or from another database?


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)
 
This I know how to do. I'm talking about controlling the number of
worksheets added.

If I want to add one (1) datatable worth of data, I only want to add one (1)
worksheet to the workbook.

The odd thing is, after adding a workbook, I do a count of the total
worksheets and get back five (5) - why it's adding 5 instead of the default
three I'm afraid to ask. I then try to delete sheets 2 thru 5, but get an
error deleting worksheet 4. It says 'Invalid Index'!? Even though it just
said there was a total count of 5 worksheets!?

That is where I stand.

Ken
 
¤ This is directly from a datatable. Nothing fancy really.
¤

It might help to know what code you are using to add the Worksheets.


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)
 
i 've pasted your code but it doesn't define the 'library' microsoft.office
......(actually i don't have this problem only in your code)
also i don't have the excel option in my .NET framework componets list.
any idea??
thank you in advance
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How to Keep Excel Sheet Formatting On a CSV Sheet with vba 1
Excel Excel spreadsheets opening at top instead of bottom. 4
How to Export data from textfile to Excel sheet in VB.NET 1
Excel, assign cells in multiple worksheets from one sheet 1
Creating Worksheets from Existing Excel Instances in VB.NET? 2
Ado.net to excel ? 3
vb.net forms connected to controls 1
Export from VB.NET 2003 to an Excel spreadsheet 3

Back
Top