N
Niranjan
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/excel/HP051992911033.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
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/excel/HP051992911033.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