Maximum worksheets in an Excel

[Guest]

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

 

[Helmut Weber]

Hi Senthil,

try:

Sub Test666()
On Error GoTo ende
start:
ActiveWorkbook.Worksheets.Add
Debug.Print ActiveWorkbook.Worksheets.Count
GoTo start
ende:
GoTo start
End Sub

I stopped it at Worksheet 928.

Saving took quite a while... ;-)

--
Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"