none
XLSM files and named ranges in Excel 2007 RRS feed

  • Question

  • Hello,

    I am trying to generate a large Excel spreadsheet apprx. 10000 rows and 40 columns. I am also generating named reference section in the Workbook.xml part of the XLSM package. Although the generation goes fine, I can not open the spreadsheet as the Excel throws an error message saying the package is corrupt.

    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

    Monday, January 25, 2010 7:23 PM

Answers

  • Hello Niranjan,

    This is not related to OpenXML SDK, I think we can use the following Excel product newsgroup which is better for the namedrange limitation topic,
    http://www.microsoft.com/office/community/en-us/default.mspx?dg=microsoft.public.excel.misc&lang=en&cr=US

    And just my opinion for the question, as the document says,
    "Names in a workbook are limited by available memory"
    Here the available memory should mean the memory in current Excel process's Virtual Address Range. So even we may still have the physical memory, when we create so many names in Excel, it can ran out of the 2GB Virtual Address.

    My suggestion is to use VMMap tool to observe the Vitural Memory allocation, as well as Working set when Excel reports the it cannot add more names. 
    http://technet.microsoft.com/en-us/sysinternals/dd535533.aspx


    Best regards,
    Ji Zhou
    MSDN Subscriber Support in Forum
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Tuesday, January 26, 2010 9:05 AM
    Moderator

All replies

  • Hello Niranjan,

    This is not related to OpenXML SDK, I think we can use the following Excel product newsgroup which is better for the namedrange limitation topic,
    http://www.microsoft.com/office/community/en-us/default.mspx?dg=microsoft.public.excel.misc&lang=en&cr=US

    And just my opinion for the question, as the document says,
    "Names in a workbook are limited by available memory"
    Here the available memory should mean the memory in current Excel process's Virtual Address Range. So even we may still have the physical memory, when we create so many names in Excel, it can ran out of the 2GB Virtual Address.

    My suggestion is to use VMMap tool to observe the Vitural Memory allocation, as well as Working set when Excel reports the it cannot add more names. 
    http://technet.microsoft.com/en-us/sysinternals/dd535533.aspx


    Best regards,
    Ji Zhou
    MSDN Subscriber Support in Forum
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Tuesday, January 26, 2010 9:05 AM
    Moderator
  • Thank you Ji,

    I have posted on the forum suggested by you. Also thanks for sharing the VM allocation tool.

    Regards,

    Niranjan
    Tuesday, January 26, 2010 5:31 PM