Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Maximum worksheets in an Excel
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" |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Maximum worksheets in an Excel.. | Excel Programming | |||
Maximum number of worksheets in XL | Excel Discussion (Misc queries) | |||
maximum number of worksheets | Excel Worksheet Functions | |||
Maximum number of worksheets in Excel 2003 | Excel Programming | |||
Maximum value from multiple worksheets | Excel Programming |