Maximum worksheets in an Excel..

S

Senthil

Hi Friends,
I am making a C# application that will create reports in Excel
from a database. My report involves creating many worksheets. Is there
any limitation for maximum worksheets in a workbook. As per Microsoft
the number of worksheets is only limitted by memory.. For me if i
create more than 256 sheets i get the exception with the value
0x800A03EC.

I get the exception here...
sheet1 = (Worksheet)book.Sheets.Add(_missing,
(object)sheet1, (object)(260), _missing);
if i keep the worksheets to be less than 256 i am able to create the
report...

Ive given the code for the reference...

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using Microsoft.Office.Interop;
using Microsoft.Office.Interop.Excel;


namespace MaxSheetsExcel
{

public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private object _missing = System.Reflection.Missing.Value;
private string _filename =
@"D:\VDC_emailExport\ExcelTemplate\MaxSheetsExcel.xls";
private string _template =
@"D:\VDC_emailExport\ExcelTemplate\Template_Def.xls";
private void button1_Click(object sender, EventArgs e)
{
//MessageBox.Show("Button Clicked!");
// excel objects
Microsoft.Office.Interop.Excel.Application ExcelApp = null;
Workbook book = null;
Worksheet sheet1 = null;
Workbooks books = null;

ExcelApp = new
Microsoft.Office.Interop.Excel.Application();
books = ExcelApp.Workbooks;
//Hesri - Instead of creating a workbook from scratch make
use of a template
//to create the report
book = ExcelApp.Workbooks.Open(_template, _missing,
_missing, _missing, _missing, _missing, _missing, _missing, _missing,
_missing, _missing, _missing, _missing, _missing,
_missing);
//book = books.Add((object)XlWBATemplate.xlWBATWorksheet);


sheet1 = (Worksheet)book.Sheets[1];
sheet1.Name = "Report_parameters";
for (int i = book.Sheets.Count; i >= 2; i--)
{
sheet1 = (Worksheet)book.Sheets;
sheet1.Delete();
}
sheet1 = (Worksheet)book.Sheets.Add(_missing,
(object)sheet1, (object)(260), _missing);

book.SaveAs((object)_filename, _missing, _missing,
_missing, _missing, (object)false, XlSaveAsAccessMode.xlExclusive,
_missing, _missing, _missing, _missing, _missing);

ReleaseComObject(sheet1);
book.Close(false, (object)_filename, false);
ExcelApp.Workbooks.Close();
ReleaseComObject(book);
ReleaseComObject(books);
ExcelApp.Quit();
ReleaseComObject(ExcelApp);
GC.Collect();
GC.WaitForPendingFinalizers();
}
private void ReleaseComObject(object ExcelObj)
{
try
{

System.Runtime.InteropServices.Marshal.ReleaseComObject(ExcelObj);
}
finally
{
ExcelObj = null;
}
}


}
}

Regards
Senthil
 
G

Guest

Hi Senthill:

I'm not sure what you are doing here, you seem to delete the sheets in the
workbook except the first one and then you add one which maybe where the
exception is coming from.

sheet1 = (Worksheet)book.Sheets[1];
sheet1.Name = "Report_parameters";
for (int i = book.Sheets.Count; i >= 2; i--)
{
sheet1 = (Worksheet)book.Sheets;
sheet1.Delete();
}
sheet1 = (Worksheet)book.Sheets.Add(_missing,
(object)sheet1, (object)(260), _missing);
Click to expand...


Also what object model are you using could that cause problems?

--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


Senthil said:
Hi Friends,
I am making a C# application that will create reports in Excel
from a database. My report involves creating many worksheets. Is there
any limitation for maximum worksheets in a workbook. As per Microsoft
the number of worksheets is only limitted by memory.. For me if i
create more than 256 sheets i get the exception with the value
0x800A03EC.

I get the exception here...
sheet1 = (Worksheet)book.Sheets.Add(_missing,
(object)sheet1, (object)(260), _missing);
if i keep the worksheets to be less than 256 i am able to create the
report...

Ive given the code for the reference...

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using Microsoft.Office.Interop;
using Microsoft.Office.Interop.Excel;


namespace MaxSheetsExcel
{

public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private object _missing = System.Reflection.Missing.Value;
private string _filename =
@"D:\VDC_emailExport\ExcelTemplate\MaxSheetsExcel.xls";
private string _template =
@"D:\VDC_emailExport\ExcelTemplate\Template_Def.xls";
private void button1_Click(object sender, EventArgs e)
{
//MessageBox.Show("Button Clicked!");
// excel objects
Microsoft.Office.Interop.Excel.Application ExcelApp = null;
Workbook book = null;
Worksheet sheet1 = null;
Workbooks books = null;

ExcelApp = new
Microsoft.Office.Interop.Excel.Application();
books = ExcelApp.Workbooks;
//Hesri - Instead of creating a workbook from scratch make
use of a template
//to create the report
book = ExcelApp.Workbooks.Open(_template, _missing,
_missing, _missing, _missing, _missing, _missing, _missing, _missing,
_missing, _missing, _missing, _missing, _missing,
_missing);
//book = books.Add((object)XlWBATemplate.xlWBATWorksheet);


sheet1 = (Worksheet)book.Sheets[1];
sheet1.Name = "Report_parameters";
for (int i = book.Sheets.Count; i >= 2; i--)
{
sheet1 = (Worksheet)book.Sheets;
sheet1.Delete();
}
sheet1 = (Worksheet)book.Sheets.Add(_missing,
(object)sheet1, (object)(260), _missing);

book.SaveAs((object)_filename, _missing, _missing,
_missing, _missing, (object)false, XlSaveAsAccessMode.xlExclusive,
_missing, _missing, _missing, _missing, _missing);

ReleaseComObject(sheet1);
book.Close(false, (object)_filename, false);
ExcelApp.Workbooks.Close();
ReleaseComObject(book);
ReleaseComObject(books);
ExcelApp.Quit();
ReleaseComObject(ExcelApp);
GC.Collect();
GC.WaitForPendingFinalizers();
}
private void ReleaseComObject(object ExcelObj)
{
try
{

System.Runtime.InteropServices.Marshal.ReleaseComObject(ExcelObj);
}
finally
{
ExcelObj = null;
}
}


}
}

Regards
Senthil
Click to expand...
 
D

Dana DeLouis

Hi. In General, I believe you can not add more than 255 sheets at one shot.

Sub Demo()
'Ok
Worksheets.Add Count:=255
'Bad
Worksheets.Add Count:=256
End Sub

But you can add more if you wish...

Sub Demo2()
'// Don't actually run this...
Do
Worksheets.Add Count:=255
Loop
End Sub

--
HTH :>)
Dana DeLouis
Windows XP & Office 2003


Senthil said:
Hi Friends,
I am making a C# application that will create reports in Excel
from a database. My report involves creating many worksheets. Is there
any limitation for maximum worksheets in a workbook. As per Microsoft
the number of worksheets is only limitted by memory.. For me if i
create more than 256 sheets i get the exception with the value
0x800A03EC.

I get the exception here...
sheet1 = (Worksheet)book.Sheets.Add(_missing,
(object)sheet1, (object)(260), _missing);
if i keep the worksheets to be less than 256 i am able to create the
report...

Ive given the code for the reference...

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using Microsoft.Office.Interop;
using Microsoft.Office.Interop.Excel;


namespace MaxSheetsExcel
{

public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private object _missing = System.Reflection.Missing.Value;
private string _filename =
@"D:\VDC_emailExport\ExcelTemplate\MaxSheetsExcel.xls";
private string _template =
@"D:\VDC_emailExport\ExcelTemplate\Template_Def.xls";
private void button1_Click(object sender, EventArgs e)
{
//MessageBox.Show("Button Clicked!");
// excel objects
Microsoft.Office.Interop.Excel.Application ExcelApp = null;
Workbook book = null;
Worksheet sheet1 = null;
Workbooks books = null;

ExcelApp = new
Microsoft.Office.Interop.Excel.Application();
books = ExcelApp.Workbooks;
//Hesri - Instead of creating a workbook from scratch make
use of a template
//to create the report
book = ExcelApp.Workbooks.Open(_template, _missing,
_missing, _missing, _missing, _missing, _missing, _missing, _missing,
_missing, _missing, _missing, _missing, _missing,
_missing);
//book = books.Add((object)XlWBATemplate.xlWBATWorksheet);


sheet1 = (Worksheet)book.Sheets[1];
sheet1.Name = "Report_parameters";
for (int i = book.Sheets.Count; i >= 2; i--)
{
sheet1 = (Worksheet)book.Sheets;
sheet1.Delete();
}
sheet1 = (Worksheet)book.Sheets.Add(_missing,
(object)sheet1, (object)(260), _missing);

book.SaveAs((object)_filename, _missing, _missing,
_missing, _missing, (object)false, XlSaveAsAccessMode.xlExclusive,
_missing, _missing, _missing, _missing, _missing);

ReleaseComObject(sheet1);
book.Close(false, (object)_filename, false);
ExcelApp.Workbooks.Close();
ReleaseComObject(book);
ReleaseComObject(books);
ExcelApp.Quit();
ReleaseComObject(ExcelApp);
GC.Collect();
GC.WaitForPendingFinalizers();
}
private void ReleaseComObject(object ExcelObj)
{
try
{

System.Runtime.InteropServices.Marshal.ReleaseComObject(ExcelObj);
}
finally
{
ExcelObj = null;
}
}


}
}

Regards
Senthil
Click to expand...
 
S

Senthil

Thanks for the prompt response guys... I was able to solve it...
Martin,
when creating an Excel there are 3 sheets. I am keeping the 1st
sheet for my report summary and deleting the remaining 2. After that
based
on the user input will create sheets that will be populated with data.
I can also use a template with only one sheet in it. Sorry for that
code which might have confused you. I am using Microsoft Excel object
11 and have offce 2003 installed in my system.
Dana,
that was terrific. It exactly works with the way that you have
suggested. Probably I will use a loop based on the user input and
create the number of sheets in the workbook rather than doing it at
one shot. By the way I am still puzzled when we can create more than
255 sheets via looping why is it not handling it when we try to create
it at one shot???

Thanks
Senthil


Hi. In General, I believe you can not add more than 255 sheets at one shot.

Sub Demo()
'Ok
Worksheets.Add Count:=255
'Bad
Worksheets.Add Count:=256
End Sub

But you can add more if you wish...

Sub Demo2()
'// Don't actually run this...
Do
Worksheets.Add Count:=255
Loop
End Sub

--
HTH :>)
Dana DeLouis
Windows XP & Office 2003



Hi Friends,
I am making a C# application that will create reports in Excel
from a database. My report involves creating many worksheets. Is there
any limitation for maximum worksheets in a workbook. As per Microsoft
the number of worksheets is only limitted by memory.. For me if i
create more than 256 sheets i get the exception with the value
0x800A03EC.
Click to expand...
I get the exception here...
sheet1 = (Worksheet)book.Sheets.Add(_missing,
(object)sheet1, (object)(260), _missing);
if i keep the worksheets to be less than 256 i am able to create the
report...
Click to expand...
Ive given the code for the reference...
Click to expand...
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using Microsoft.Office.Interop;
using Microsoft.Office.Interop.Excel;
Click to expand...
namespace MaxSheetsExcel
{
Click to expand...
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private object _missing = System.Reflection.Missing.Value;
private string _filename =
@"D:\VDC_emailExport\ExcelTemplate\MaxSheetsExcel.xls";
private string _template =
@"D:\VDC_emailExport\ExcelTemplate\Template_Def.xls";
private void button1_Click(object sender, EventArgs e)
{
//MessageBox.Show("Button Clicked!");
// excel objects
Microsoft.Office.Interop.Excel.Application ExcelApp = null;
Workbook book = null;
Worksheet sheet1 = null;
Workbooks books = null;
Click to expand...
ExcelApp = new
Microsoft.Office.Interop.Excel.Application();
books = ExcelApp.Workbooks;
//Hesri - Instead of creating a workbook from scratch make
use of a template
//to create the report
book = ExcelApp.Workbooks.Open(_template, _missing,
_missing, _missing, _missing, _missing, _missing, _missing, _missing,
_missing, _missing, _missing, _missing, _missing,
_missing);
//book = books.Add((object)XlWBATemplate.xlWBATWorksheet);
Click to expand...
sheet1 = (Worksheet)book.Sheets[1];
sheet1.Name = "Report_parameters";
for (int i = book.Sheets.Count; i >= 2; i--)
{
sheet1 = (Worksheet)book.Sheets;
sheet1.Delete();
}
sheet1 = (Worksheet)book.Sheets.Add(_missing,
(object)sheet1, (object)(260), _missing);
Click to expand...

book.SaveAs((object)_filename, _missing, _missing,
_missing, _missing, (object)false, XlSaveAsAccessMode.xlExclusive,
_missing, _missing, _missing, _missing, _missing);
Click to expand...
ReleaseComObject(sheet1);
book.Close(false, (object)_filename, false);
ExcelApp.Workbooks.Close();
ReleaseComObject(book);
ReleaseComObject(books);
ExcelApp.Quit();
ReleaseComObject(ExcelApp);
GC.Collect();
GC.WaitForPendingFinalizers();
}
private void ReleaseComObject(object ExcelObj)
{
try
{
Click to expand...
System.Runtime.InteropServices.Marshal.ReleaseComObject(ExcelObj);
}
finally
{
ExcelObj = null;
}
}

Regards
Senthil- Hide quoted text -- Show quoted text -
Click to expand...
Click to expand...
 
J

Jon Peltier

when creating an Excel there are 3 sheets. I am keeping the 1st
sheet for my report summary and deleting the remaining 2.
Click to expand...

Go to Tools menu > Options > General tab. Change the Sheets in New Workbook
setting to 1.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


Senthil said:
Thanks for the prompt response guys... I was able to solve it...
Martin,
when creating an Excel there are 3 sheets. I am keeping the 1st
sheet for my report summary and deleting the remaining 2. After that
based
on the user input will create sheets that will be populated with data.
I can also use a template with only one sheet in it. Sorry for that
code which might have confused you. I am using Microsoft Excel object
11 and have offce 2003 installed in my system.
Dana,
that was terrific. It exactly works with the way that you have
suggested. Probably I will use a loop based on the user input and
create the number of sheets in the workbook rather than doing it at
one shot. By the way I am still puzzled when we can create more than
255 sheets via looping why is it not handling it when we try to create
it at one shot???

Thanks
Senthil


Hi. In General, I believe you can not add more than 255 sheets at one
shot.

Sub Demo()
'Ok
Worksheets.Add Count:=255
'Bad
Worksheets.Add Count:=256
End Sub

But you can add more if you wish...

Sub Demo2()
'// Don't actually run this...
Do
Worksheets.Add Count:=255
Loop
End Sub

--
HTH :>)
Dana DeLouis
Windows XP & Office 2003

message

Hi Friends,
I am making a C# application that will create reports in Excel
from a database. My report involves creating many worksheets. Is there
any limitation for maximum worksheets in a workbook. As per Microsoft
the number of worksheets is only limitted by memory.. For me if i
create more than 256 sheets i get the exception with the value
0x800A03EC.
Click to expand...
I get the exception here...
sheet1 = (Worksheet)book.Sheets.Add(_missing,
(object)sheet1, (object)(260), _missing);
if i keep the worksheets to be less than 256 i am able to create the
report...
Click to expand...
Ive given the code for the reference...
Click to expand...
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using Microsoft.Office.Interop;
using Microsoft.Office.Interop.Excel;
Click to expand...
namespace MaxSheetsExcel
{
Click to expand...
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private object _missing = System.Reflection.Missing.Value;
private string _filename =
@"D:\VDC_emailExport\ExcelTemplate\MaxSheetsExcel.xls";
private string _template =
@"D:\VDC_emailExport\ExcelTemplate\Template_Def.xls";
private void button1_Click(object sender, EventArgs e)
{
//MessageBox.Show("Button Clicked!");
// excel objects
Microsoft.Office.Interop.Excel.Application ExcelApp = null;
Workbook book = null;
Worksheet sheet1 = null;
Workbooks books = null;
Click to expand...
ExcelApp = new
Microsoft.Office.Interop.Excel.Application();
books = ExcelApp.Workbooks;
//Hesri - Instead of creating a workbook from scratch make
use of a template
//to create the report
book = ExcelApp.Workbooks.Open(_template, _missing,
_missing, _missing, _missing, _missing, _missing, _missing, _missing,
_missing, _missing, _missing, _missing, _missing,
_missing);
//book = books.Add((object)XlWBATemplate.xlWBATWorksheet);
Click to expand...
sheet1 = (Worksheet)book.Sheets[1];
sheet1.Name = "Report_parameters";
for (int i = book.Sheets.Count; i >= 2; i--)
{
sheet1 = (Worksheet)book.Sheets;
sheet1.Delete();
}
sheet1 = (Worksheet)book.Sheets.Add(_missing,
(object)sheet1, (object)(260), _missing);
Click to expand...

book.SaveAs((object)_filename, _missing, _missing,
_missing, _missing, (object)false, XlSaveAsAccessMode.xlExclusive,
_missing, _missing, _missing, _missing, _missing);
Click to expand...
ReleaseComObject(sheet1);
book.Close(false, (object)_filename, false);
ExcelApp.Workbooks.Close();
ReleaseComObject(book);
ReleaseComObject(books);
ExcelApp.Quit();
ReleaseComObject(ExcelApp);
GC.Collect();
GC.WaitForPendingFinalizers();
}
private void ReleaseComObject(object ExcelObj)
{
try
{
Click to expand...
System.Runtime.InteropServices.Marshal.ReleaseComObject(ExcelObj);
}
finally
{
ExcelObj = null;
}
}

Regards
Senthil- Hide quoted text -- Show quoted text -
Click to expand...
Click to expand...
Click to expand...
 

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

Maximum worksheets in an Excel 1
Deleting a worksheet - impossible! 4
How do you import sheets into an Excel XLA Add-In? 8
Excel working with recording macro in excel 1
Hyperlink jpg files in Dir via VBA 1
best way to read specific record from Excel file in C# 1
Problem: How to copy excel sheet in C++ 0
Bug in Excel 2007 using Ranges/Names? 2

Top