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

[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...b;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.

--
Ken

[Ken Tucker [MVP]]

Hi,

Dim oExcel As Microsoft.Offic e.Interop.Excel .Application

Dim oBook, oBook1 As Microsoft.Offic e.Interop.Excel .Workbook

Dim oSheet As Microsoft.Offic e.Interop.Excel .Worksheet

'Start a new workbook in Excel.

oExcel = New Microsoft.Offic e.Interop.Excel .Application

oBook = oExcel.Workbook s.Add

oBook1 = oExcel.Workbook s.Add

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

oSheet = CType(oBook.Wor ksheets(1),
Microsoft.Offic e.Interop.Excel .Worksheet)

oSheet.Range("A 1").Value = "Last Name"

oSheet.Range("B 1").Value = "First Name"

oSheet.Range("C 1").Value = "Price"

oSheet.Range("A 1:B1").Font.Bol d = True

oSheet.Range("A 2").Value = "Doe"

oSheet.Range("B 2").Value = "John"

oSheet.Range("C 2").Value = 12345.456

oSheet.Range("C 2").Cells.Numbe rFormat = "$0.00"

oSheet = CType(oBook.Wor ksheets(2),
Microsoft.Offic e.Interop.Excel .Worksheet)

oSheet.Range("A 1").Value = "Last Name"

oSheet.Range("B 1").Value = "First Name"

oSheet.Range("C 1").Value = "Price"

oSheet.Range("A 1:B1").Font.Bol d = True

oSheet.Range("A 2").Value = "Doe"

oSheet.Range("B 2").Value = "John"

oSheet.Range("C 2").Value = 12345.456

oSheet.Range("C 2").Cells.Numbe rFormat = "$0.00"

'Save the Workbook and quit Excel.

oExcel.DisplayA lerts = False

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

oSheet = Nothing

oBook = Nothing

oExcel.Quit()

oExcel = Nothing

GC.Collect()

Ken

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

"KC" <yo*@dontneed.t his> wrote in message
news:e2******** ******@TK2MSFTN GP11.phx.gbl...
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...b;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.

--
Ken

[scorpion53061]

http://www.kjmsolutions.com/datasetarray.htm

_______________ _______________ __

From: KC [mailto:yo*@dont need.this]
Sent: Wednesday, August 25, 2004 4:55 PM
To: microsoft.publi c.dotnet.langua ges.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...b;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.

--
Ken

[Paul Clement]

On Wed, 25 Aug 2004 16:55:22 -0500, "KC" <yo*@dontneed.t his> wrote:

¤ 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...b;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 ~~~ pc******@amerit ech.net
Microsoft MVP (Visual Basic)

[KC]

This is directly from a datatable. Nothing fancy really.

Ken
"Paul Clement" <Us************ ***********@sws pectrum.com> wrote in message
news:st******** *************** *********@4ax.c om...

On Wed, 25 Aug 2004 16:55:22 -0500, "KC" <yo*@dontneed.t his> wrote:

¤ 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...b;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 ~~~ pc******@amerit ech.net
Microsoft MVP (Visual Basic)

[KC]

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
"Ken Tucker [MVP]" <vb***@bellsout h.net> wrote in message
news:ex******** ******@TK2MSFTN GP15.phx.gbl...

Hi,

Dim oExcel As Microsoft.Offic e.Interop.Excel .Application

Dim oBook, oBook1 As Microsoft.Offic e.Interop.Excel .Workbook

Dim oSheet As Microsoft.Offic e.Interop.Excel .Worksheet

'Start a new workbook in Excel.

oExcel = New Microsoft.Offic e.Interop.Excel .Application

oBook = oExcel.Workbook s.Add

oBook1 = oExcel.Workbook s.Add

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

oSheet = CType(oBook.Wor ksheets(1),
Microsoft.Offic e.Interop.Excel .Worksheet)

oSheet.Range("A 1").Value = "Last Name"

oSheet.Range("B 1").Value = "First Name"

oSheet.Range("C 1").Value = "Price"

oSheet.Range("A 1:B1").Font.Bol d = True

oSheet.Range("A 2").Value = "Doe"

oSheet.Range("B 2").Value = "John"

oSheet.Range("C 2").Value = 12345.456

oSheet.Range("C 2").Cells.Numbe rFormat = "$0.00"

oSheet = CType(oBook.Wor ksheets(2),
Microsoft.Offic e.Interop.Excel .Worksheet)

oSheet.Range("A 1").Value = "Last Name"

oSheet.Range("B 1").Value = "First Name"

oSheet.Range("C 1").Value = "Price"

oSheet.Range("A 1:B1").Font.Bol d = True

oSheet.Range("A 2").Value = "Doe"

oSheet.Range("B 2").Value = "John"

oSheet.Range("C 2").Value = 12345.456

oSheet.Range("C 2").Cells.Numbe rFormat = "$0.00"

'Save the Workbook and quit Excel.

oExcel.DisplayA lerts = False

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

oSheet = Nothing

oBook = Nothing

oExcel.Quit()

oExcel = Nothing

GC.Collect()

Ken

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

"KC" <yo*@dontneed.t his> wrote in message
news:e2******** ******@TK2MSFTN GP11.phx.gbl...
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...b;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.

--
Ken

[Paul Clement]

On Thu, 26 Aug 2004 11:05:16 -0500, "KC" <yo*@dontneed.t his> wrote:

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

It might help to know what code you are using to add the Worksheets.
Paul ~~~ pc******@amerit ech.net
Microsoft MVP (Visual Basic)

[John]

i 've pasted your code but it doesn't define the 'library' microsoft.offic e
......(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

"Ken Tucker [MVP]" wrote:

Hi,

Dim oExcel As Microsoft.Offic e.Interop.Excel .Application

Dim oBook, oBook1 As Microsoft.Offic e.Interop.Excel .Workbook

Dim oSheet As Microsoft.Offic e.Interop.Excel .Worksheet

'Start a new workbook in Excel.

oExcel = New Microsoft.Offic e.Interop.Excel .Application

oBook = oExcel.Workbook s.Add

oBook1 = oExcel.Workbook s.Add

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

oSheet = CType(oBook.Wor ksheets(1),
Microsoft.Offic e.Interop.Excel .Worksheet)

oSheet.Range("A 1").Value = "Last Name"

oSheet.Range("B 1").Value = "First Name"

oSheet.Range("C 1").Value = "Price"

oSheet.Range("A 1:B1").Font.Bol d = True

oSheet.Range("A 2").Value = "Doe"

oSheet.Range("B 2").Value = "John"

oSheet.Range("C 2").Value = 12345.456

oSheet.Range("C 2").Cells.Numbe rFormat = "$0.00"

oSheet = CType(oBook.Wor ksheets(2),
Microsoft.Offic e.Interop.Excel .Worksheet)

oSheet.Range("A 1").Value = "Last Name"

oSheet.Range("B 1").Value = "First Name"

oSheet.Range("C 1").Value = "Price"

oSheet.Range("A 1:B1").Font.Bol d = True

oSheet.Range("A 2").Value = "Doe"

oSheet.Range("B 2").Value = "John"

oSheet.Range("C 2").Value = 12345.456

oSheet.Range("C 2").Cells.Numbe rFormat = "$0.00"

'Save the Workbook and quit Excel.

oExcel.DisplayA lerts = False

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

oSheet = Nothing

oBook = Nothing

oExcel.Quit()

oExcel = Nothing

GC.Collect()

Ken

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

"KC" <yo*@dontneed.t his> wrote in message
news:e2******** ******@TK2MSFTN GP11.phx.gbl...
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...b;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.

--
Ken