Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Problem with named range for a large spreadsheet in Excel 2007
Hello,
I am trying to generate a large Excel spreadsheet apprx. 10000 rows and 40 columns. I am generating defined name section in the Workbook.xml part of the XLSM package. Here is a sample entry from that section <definedName name="_._44802_._0_._0_._0_._top_line" localSheetId="0" hidden="1"Sheet1!$B$2</definedName Although the generation goes fine, I can not open the spreadsheet as the Excel throws an error message saying the package is corrupt. But this is not the case if the spreadsheet is small say, 200 rows by 10 columns. Further investigation indicated that there is a limit on the number of named ranges one can add to the Excel. However, that limit is governed by the available memory on the client machine as per this article http://office.microsoft.com/en-us/ex...992911033.aspx To test out the scenario, i wrote a small VBA macro to add the named ranges to the cells. Note: Here one cell = one named range, which is also the case in my file generation logic. Sub addNames() Dim objRange As Range Dim actSheet As Excel.Worksheet For r = 1 To 10000 For c = 1 To 10 Set actSheet = Excel.ActiveSheet Set objRange = actSheet.Cells(r, c) Set nm = ActiveWorkbook.Names.Add("NMR" & r & "C" & c, objRange) Next Next End Sub After 65472 cells, excel does not allow me to add more than the specified amount of names. This is regardless of whatever RAM, whatever amount of memory is available on user's machine. My question is: Is there a limit on number of names(named ranges) we can add to excel 2007? And how to overcome this limit so that generated spreadsheet can open? Thanks, Niranjan |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2007 - Find formulas that use a named range | Excel Discussion (Misc queries) | |||
Large excel file problem when moved from 2003 to 2007 | Excel Discussion (Misc queries) | |||
Excel 2007 hangs on any change to large range of data | Excel Discussion (Misc queries) | |||
Large file sizes after modifying spreadsheet in Excel 2007. | Excel Discussion (Misc queries) | |||
Vlookup in large named range | Excel Worksheet Functions |