7

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?

Bobby
  • 9,032
rlb.usa
  • 1,548

5 Answers5

7

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

Lunatik
  • 5,591
6

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.

malat
  • 1,374
Jarvin
  • 7,386
3

Official Excel limits say it should be fine:

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

Gareth
  • 19,080
Adam Butler
  • 581
1

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.

Gareth
  • 19,080
Tom Pham
  • 11
1

In Excel 2013 (and I assume in Excel 2007 and later) the length of a string displayed in a single cell is 1024 characters. I discovered this by accident when I wrote a longer string created in VBA into a single Excel cell, which was the last cell in the column to contain data. The usual display of a long string in cells to the right terminated after 1024 characters. However, the len function correctly returned the actual size of the string and copying the string (which was a list of email addresses) and pasting it into Outlook gave the correct string. It seems that the longer string available in Excel 2007 and later work fine, but will not display completely.

LSC
  • 11
  • 1