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

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

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

"KC" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
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

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



________________________________

From: KC [private.php?do=newpm&u=]
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...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

On Wed, 25 Aug 2004 16:55:22 -0500, "KC" <(E-Mail Removed)> 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 ~~~ (E-Mail Removed)
Microsoft MVP (Visual Basic)

This is directly from a datatable. Nothing fancy really.

Ken


"Paul Clement" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> On Wed, 25 Aug 2004 16:55:22 -0500, "KC" <(E-Mail Removed)> 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 ~~~ (E-Mail 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


"Ken Tucker [MVP]" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>
> -------------------------------------
>
> "KC" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> 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
>
>
>

On Thu, 26 Aug 2004 11:05:16 -0500, "KC" <(E-Mail Removed)> 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 ~~~ (E-Mail 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

"Ken Tucker [MVP]" wrote:

> 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
>
> -------------------------------------
>
> "KC" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> 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
>
>
>
>