none
Data Validation in Excel 2007

    Question

  •  

    Hi,

     

    Is there a limit on the comma delimited list for Data Validation in Excel 2007?

     

    I know that it was 255 character limit per cell in previous versions of Excel, and I found that new number of characters that can be stored in a cell formatted as Text is 32k in Excel 2007.

     

    I am populating Data Validation list programmatically (from Excel Addin written in C#), and I can add really long list of items. Excel 2007 can will save those long Validation lists without any problem, and when I open file in XML format (Sheet1.xml) it shows that all the items in Data Validation list are saved correctly.

     

    The problem is when I try to reopen xlsx file again, I am getting an error (Excel found unreadable content...) and after that ‘Excel Removed Feature: Data Validation...’. This is happening only if the comma delimited list has more that 255 characters.

     

    Due to design issues I cannot use ranges to populate Data Validation lists.

     

    I am wondering if this is Excel 2007 limitation, and is there any way to read the list from XML and repopulate Data Validation because the entire list is saved in XML format. Any suggestion will be helpful.

     

     

    Thanks.

    Aleksandra
    Wednesday, January 30, 2008 11:03 PM

Answers

  •  

    Hi galexyu,

     

    After conslting my colleague from the Excel team we have found out that there is not way to skip Excel error messages and use XML to read and repopulate lists.

     

     

    Thanks,

     

    Harjit.

    Wednesday, February 20, 2008 7:05 PM

All replies

  • Hi,

     

    Could you please the following details to proceed with this inquiry:

       - How is the addin adding the validation csv text?  Is it being added directly to the XML or to a worksheet object or another way?  If an Excel object, which object and how?

       - Can this be reproduced manually without the code?

     

    Thanks,

     

    Harjit

     

    Monday, February 04, 2008 9:03 PM
  • Hi,

    Thanks for reply.

     

    I will answer the second question first. I don't think that it can be reproduced without a code. I've tried adding manually longer list through Data Validation dialog in Excel, but the Source field accepts only 255 characters.

     

    The Addin is adding the lists directly into Excel worksheet. I am using Range object to populate the list. Here is the part of the code:

     

    ...

    object List = new object();

    ...

    //call function to create comma separated list 

    List = ArrayToList(validationList, false);

    ...

    tmpRange.Validation.Add(Microsoft.Office.Interop.Excel.XlDVType.xlValidateList,

    Microsoft.Office.Interop.Excel.XlDVAlertStyle.xlValidAlertInformation,

    Microsoft.Office.Interop.Excel.XlFormatConditionOperator.xlEqual,

    List, Missing.Value);

    tmpRange.Validation.IgnoreBlank = false;

    tmpRange.Validation.InCellDropdown = true;

    ....

     

    I can see entire long lists populated in Excel using the code above. I can save Excel file without any problems. When I re-open file in XML I can see the entire lists again. Here is the XML part from Sheet1.xml:

     

    <dataValidations count="5">

    <dataValidation type="list" errorStyle="information" operator="equal" showInputMessage="1" showErrorMessage="1" sqref="A8">

    <formula1>"'1000,'1040,'1050,'1060,'1100,'1110,'1130,'1200,'1210,'1230,'1240,'1250,'1300,'2000,'2100,'2110,'2200,'2210,'3000,'3010,'3030,'3200,'3300,'3400,'3410,'3420,'3430,'3440,'3450,'3460,'3470,'3500,'3600,'3700,'3800,'3900,'4000,'5000,'5500,'6000,'6010,'6100,'6200,'7000,'7010,'7070,'7080,'7090,'7115,'7120,'7125,'7130,'7145,'7150,'7155,'7160,'7200,'7250,'7275,'7300,'7350,'7400,'7500,'7550,'7560,'7600,'7650,'7700,'7740,'7750,'7760,'7800,'7850,'7999,'8000,'8100,'8200,'8300,'8400,'8500,'9000,'9100,'9200"</formula1>

    </dataValidation>

    <dataValidation type="list" errorStyle="information" operator="equal" showInputMessage="1" showErrorMessage="1" sqref="B2">

    <formula1>"'000,'010,'020,'030,'040"</formula1>

    </dataValidation><dataValidation type="list" errorStyle="information" operator="equal" showInputMessage="1" showErrorMessage="1" sqref="E6">

    <formula1>"'January,'February,'March,'April,'May,'June,'July,'August,'September,'October,'November,'December,'This Month,'Last Month,'Month 1,'Month 2,'Month 3,'Month 4,'Month 5,'Month 6,'Month 7,'Month 8,'Month 9,'Month 10,'Month 11,'Month 12,'Period 13,'Month 1 Change,'Month 2 Change,'Month 3 Change,'Month 4 Change,'Month 5 Change,'Month 6 Change,'Month 7 Change,'Month 8 Change,'Month 9 Change,'Month 10 Change,'Month 11 Change,'Month 12 Change,'Period 13 Change,'Last Year Month 1,'Last Year Month 2,'Last Year Month 3,'Last Year Month 4,'Last Year Month 5,'Last Year Month 6,'Last Year Month 7,'Last Year Month 8,'Last Year Month 9,'Last Year Month 10,'Last Year Month 11,'Last Year Month 12,'Last Year Period 13,'This Month Last Year,'This Month Budget,'This Quarter,'This Quarter Last Year,'This Quarter Budget,'This Quarter Last Year Budget,'Year to Date,'Year to Date Budget,'Year to Date Last Year,'Year to Date Last Year Budget,'1 Month Ago,'2 Months Ago,'3 Months Ago,'4 Months Ago,'5 Months Ago,'6 Months Ago,'7 Months Ago,'8 Months Ago,'9 Months Ago,'10 Months Ago,'11 Months Ago,'12 Months Ago,'Year to date Month 1,'Year to date Month 2,'Year to date Month 3,'Year to date Month 4,'Year to date Month 5,'Year to date Month 6,'Year to date Month 7,'Year to date Month 8,'Year to date Month 9,'Year to date Month 10,'Year to date Month 11,'Year to date Month 12,'Quarter 1,'Quarter 2,'Quarter 3,'Quarter 4,'Quarter 1 Budget,'Quarter 2 Budget,'Quarter 3 Budget,'Quarter 4 Budget,'1 Budget,'2 Budget,'3 Budget,'4 Budget,'5 Budget,'6 Budget,'7 Budget,'8 Budget,'9 Budget,'10 Budget,'11 Budget,'12 Budget,'Year to date Month 1 Change,'Year to date Month 2 Change,'Year to date Month 3 Change,'Year to date Month 4 Change,'Year to date Month 5 Change,'Year to date Month 6 Change,'Year to date Month 7 Change,'Year to date Month 8 Change,'Year to date Month 9 Change,'Year to date Month 10 Change,'Year to date Month 11 Change,'Year to date Month 12 Change"</formula1>

    </dataValidation><dataValidation type="list" errorStyle="information" operator="equal" showInputMessage="1" showErrorMessage="1" sqref="B5">

    <formula1>"'2010,'2009,'2008,'2007,'2006,'2005,'2004,'2003,'2002,'2001"</formula1>

    </dataValidation>

    <dataValidation type="list" errorStyle="information" operator="equal" showInputMessage="1" showErrorMessage="1" sqref="B6">

    <formula1>"'Current,'Next,'Last"</formula1>

    </dataValidation>

    </dataValidations>

     

    So the lists are there, saved in XML format, just Excel itself has problems to read them from XML. If this is an Excel limitation, not to be able to read Data Validation lists longer than 255 characters, I was wondering:

    - Can I avoid Excel Error messages about 'Unreadable content' and 'Removed feature: Data Validation from sheet1.xml part'?

    - Can my Addin read lists from XML and repopulate them?

     

    I will appreciate any suggestion. Thanks.

    Aleksandra

     

    Tuesday, February 05, 2008 6:57 PM
  • Hi,

     

    I will need to check the exact senario, meanwhile could you validate that you are having the START and CLOSING single quotes for those validation strings. As in for example

     

    <formula1>"'January,'February,'March,'April,

     

    I don't see the closing quotes in there. This might be causing the Excel to error.

     

    Thanks,

    -RaviAngu

     

    Thursday, February 14, 2008 12:34 AM
  •  

    Hi,

     

    I’ve changed the code to create lists without single quotes, and it does not help.

     

    Single quotes are not a problem, they are just for Excel to treat a cell value as a string. If I have triple zero 000 on the list, without leading single quote, in Excel it will be displayed as single zero 0, but ‘000 will show all 3 zeros in a cell.

    Also XML file with long lists is saved by Excel without any problems (I am not creating XML in code). Excel just has a problem reading long lists from XML.

     

    Here is the entire list in XML saved by Excel, but Excel can not read from it:

     

      <dataValidation type="list" errorStyle="information" operator="equal" showInputMessage="1" showErrorMessage="1" sqref="A8">    <formula1>"1000,1040,1050,1060,1100,1110,1130,1200,1210,1230,1240,1250,1300,2000,2100,2110,2200,2210,3000,3010,3030,3200,3300,3400,3410,3420,3430,3440,3450,3460,3470,3500,3600,3700,3800,3900,4000,5000,5500,6000,6010,6100,6200,7000,7010,7070,7080,7090,7115,7120,7125,7130,7145,7150,7155,7160,7200,7250,7275,7300,7350,7400,7500,7550,7560,7600,7650,7700,7740,7750,7760,7800,7850,7999,8000,8100,8200,8300,8400,8500,9000,9100,9200"</formula1>

      </dataValidation>

     

    If I make list shorter to 254 characters, Excel has no problems reading it:

     

      <dataValidation type="list" errorStyle="information" operator="equal" showInputMessage="1" showErrorMessage="1" sqref="A8">

    <formula1>"1000,1040,1050,1060,1100,1110,1130,1200,1210,1230,1240,1250,1300,2000,2100,2110,2200,2210,3000,3010,3030,3200,3300,3400,3410,3420,3430,3440,3450,3460,3470,3500,3600,3700,3800,3900,4000,5000,5500,6000,6010,6100,6200,7000,7010,7070,7080,7090,7115,7120,7125"</formula1>

      </dataValidation>

     

    But if I add only one more character at the end, Excel is returning an error:

     

      <dataValidation type="list" errorStyle="information" operator="equal" showInputMessage="1" showErrorMessage="1" sqref="A8">

    <formula1>"1000,1040,1050,1060,1100,1110,1130,1200,1210,1230,1240,1250,1300,2000,2100,2110,2200,2210,3000,3010,3030,3200,3300,3400,3410,3420,3430,3440,3450,3460,3470,3500,3600,3700,3800,3900,4000,5000,5500,6000,6010,6100,6200,7000,7010,7070,7080,7090,7115,7120,7125,a"</formula1>

      </dataValidation>

     

    Thanks.

     

    Aleksandra

    Thursday, February 14, 2008 5:59 PM
  • Hi Aleksandra,

     

    There is a limitation of 255 characters if you are typing the validation text in manually. Please folllow the below steps to write the data to the vallidation list

     

    - Use the list functionailty in Excel.

    - Write to a range.

    - Name the range.

    - Set the validation to that named range.

     

    Thanks,

     

    Harjit.

    Monday, February 18, 2008 8:22 PM
  • I know that there is a limit of 255 characters if you are typing list manually. I mentioned it before because someone asked me if the problem can be replicated manually, without writing a code. I think I was explaining too much, so you are not getting the point of my question.

     

    In short:

    1) I am creating Data Validation list programmatically from C# code (code is posted in one of the replies above):

    2) Excel is displaying entire long list and saving it without problems.

    3) When I open saved Excel file in XML I can see entire long list (example is posted in one of the replies above).

    4) PROBLEM is when I want to reopen saved file, Excel has problem reading saved list, and it removes Data Validation feature if the list is longer that 255 characters.

     

    My question is again, is there a way that I can skip Excel error messages and use XML to read and repopulate lists that are correctly saved in XML.

    Or any other suggestion how to overcome this limitation. Due to the design issues I can not use ranges.

     

    Thanks again.

     

    Aleksandra

     

    Tuesday, February 19, 2008 6:53 PM
  •  

    Hi galexyu,

     

    After conslting my colleague from the Excel team we have found out that there is not way to skip Excel error messages and use XML to read and repopulate lists.

     

     

    Thanks,

     

    Harjit.

    Wednesday, February 20, 2008 7:05 PM