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

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

P: n/a
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
Share this Question
Share on Google+
7 Replies


P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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.