Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Excel is refusing to open some of the files I programmaticaly create. I think what is happening is that it pukes because a particular column has very long strings (up to 1850 chars), but for the purposes of troubleshooting when I make the strings shorter (900 chars), it opens.

Question #1 : What is the longest string I can fit in a cell? Some google results say 1024 or 7,000ish but I have found 1000 to be too long.

Question #2 : I've tried writing my files in both XML and XLS format, but both have this same string-length-problem. Is there a format that Excel will read that will allow me to fit my 1850 length strings?

share|improve this question
    
that's a big freakin # –  KronoS Aug 30 '10 at 21:28
    
1,800ish is the size of three modest paragraphs. –  rlb.usa Aug 30 '10 at 21:55
    
You need to specify your version of excel, as this is one of the many things that is different between the versions. –  Jarvin Aug 30 '10 at 21:56
    
It's MS Excel 2007 –  rlb.usa Aug 30 '10 at 21:58

4 Answers 4

up vote 3 down vote accepted

According to this there was a 1k characters per cell limit in excel 2003 which increased to a 32k characters per cell limit in excel 2007. If your program is programmaticaly creating an excel 2003 (.xls) and not a excel 2007 (.xlsx) file, then this could be causing the issue since the .xls format doesn't support more than 1k characters per cell.

You shouldn't be having issues with a XML or XLS format going directly into Excel 2007, since excel 2003's limit shouldn't be involved, but I can't test it out since I don't own Excel 2007, but hopefully this has been helpful regardless.

share|improve this answer
    
I think that your answer is correct, but it doesn't help me, and it still rejects my 1,800 << 32,000 characters. I'm going to wait and see if anyone can come up with any thing. –  rlb.usa Aug 30 '10 at 23:10

Even Excel 2003 can easily handle 32,767 characters in a cell, but versions before Excel 2007 only display the first 1024 characters in-cell (MS source).

You may have run into a known issue that truncates strings at 911 characters. I've experienced this before when transferring data over an ADO connection.

The following link pertains to Excel 2003 but it certainly sounds like the same problem you are having.

http://support.microsoft.com/kb/818808/en-us

share|improve this answer

Official Excel limits say it should be fine:

http://office.microsoft.com/en-us/excel-help/excel-specifications-and-limits-HP010073849.aspx

share|improve this answer
    
yup this is the right link. –  Anonymous Type Mar 13 '13 at 21:14

In case you get your data from ADO, try the codes below. It worked for me.

Activecell.value=LEFT(YourRecordset("FieldName"), LEN(YourRecordset("FieldName")))

Basically, you get the whole content of the field, but in a different way. Since the length of the contents over 900 (to my experience), you have to use an alternative way. You do not have to stick with the LEFT function; the RIGHT function also works.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.