471,831 Members | 1,613 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,831 software developers and data experts.

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
Nov 21 '05 #1
7 11458
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" <yo*@dontneed.this> wrote in message
news:e2**************@TK2MSFTNGP11.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

Nov 21 '05 #2
http://www.kjmsolutions.com/datasetarray.htm

________________________________

From: KC [mailto:yo*@dontneed.this]
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
Nov 21 '05 #3
On Wed, 25 Aug 2004 16:55:22 -0500, "KC" <yo*@dontneed.this> 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******@ameritech.net
Microsoft MVP (Visual Basic)
Nov 21 '05 #4
KC
This is directly from a datatable. Nothing fancy really.

Ken
"Paul Clement" <Us***********************@swspectrum.com> wrote in message
news:st********************************@4ax.com...
On Wed, 25 Aug 2004 16:55:22 -0500, "KC" <yo*@dontneed.this> 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******@ameritech.net
Microsoft MVP (Visual Basic)

Nov 21 '05 #5
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***@bellsouth.net> wrote in message
news:ex**************@TK2MSFTNGP15.phx.gbl...
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" <yo*@dontneed.this> wrote in message
news:e2**************@TK2MSFTNGP11.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

Nov 21 '05 #6
On Thu, 26 Aug 2004 11:05:16 -0500, "KC" <yo*@dontneed.this> 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******@ameritech.net
Microsoft MVP (Visual Basic)
Nov 21 '05 #7
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" <yo*@dontneed.this> wrote in message
news:e2**************@TK2MSFTNGP11.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

Nov 21 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

7 posts views Thread by Martin | last post: by
27 posts views Thread by jeniffer | last post: by
1 post views Thread by Venkat | last post: by
reply views Thread by kennedystephen | last post: by
8 posts views Thread by yogarajan | last post: by
4 posts views Thread by =?Utf-8?B?Sm9zaW4gSm9obg==?= | last post: by
NeoPa
reply views Thread by NeoPa | last post: by
aboka
reply views Thread by aboka | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.