Maximum worksheets in an Excel..
 

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.xl s";
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[i];
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.ReleaseComO bject(ExcelObj);
}
finally
{
ExcelObj = null;
}
}


}
}

Regards
Senthil

Maximum worksheets in an Excel..
 

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.

[i]
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);

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" wrote:
[i]
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.xl s";
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.ReleaseComO bject(ExcelObj);
}
finally
{
ExcelObj = null;
}
}


}
}

Regards
Senthil

Maximum worksheets in an Excel..
 

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" wrote in message
ups.com...[i]
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.xl s";
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.ReleaseComO bject(ExcelObj);
}
finally
{
ExcelObj = null;
}
}


}
}

Regards
Senthil

Maximum worksheets in an Excel..
 

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


On Jan 27, 10:56 pm, "Dana DeLouis" wrote:[i]
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" wrote in oglegroups.com...



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.xl s";
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.ReleaseComO bject(ExcelObj);
}
finally
{
ExcelObj = null;
}
}

}
}

Regards
Senthil- Hide quoted text -- Show quoted text -

Maximum worksheets in an Excel..
 

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

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" wrote in message
ups.com...[i]
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


On Jan 27, 10:56 pm, "Dana DeLouis" wrote:
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" wrote in
oglegroups.com...



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.xl s";
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.ReleaseComO bject(ExcelObj);
}
finally
{
ExcelObj = null;
}
}

}
}

Regards
Senthil- Hide quoted text -- Show quoted text -