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 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 - |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Is there a maximum of the number of worksheets in a workbook? | Excel Discussion (Misc queries) | |||
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 |