Excel cutting out down over 1024 characters

Question

I am using Excel 2003 to save a large file as a CSV.

But when saving cells that contain over 1024 characters, it cuts out the characters beyond 1024.

Per a previous question, I am using this official macro to save: http://support.microsoft.com/default.aspx?scid=kb;en-us;291296&Product=xlw

This macro in question is probably causing it, since I'm not using the normal Save As (in order to put quotes around every field).

It may not be 1024 characters, but long cells are getting cut off. What in this macro is causing that?

Answer 1

The code is too clever for it's own good. It uses the Text property, which is what is displayed in a cell, to get a text representation of a cells value. The problem as mentioned in another answer is this is limited to 1024 characters. You can change the code to use the Value property but this might not work on all cell value types.

Change this line:

Print #FileNum, """" & Selection.Cells(RowCount, ColumnCount).Text & """";

To:

Print #FileNum, """" & Selection.Cells(RowCount, ColumnCount).Value & """";

Answer 2

Actually, the data entered in the spreadsheet is still present in the cell. You can verify this by selecting the cell and looking at the formula bar.

However, Excel only displays up to 1024 characters.

When you save your spreadsheet as a CSV file, the physical data should be exported as you expect because it was never really lost. Here is Microsoft's guidance on this:

http://support.microsoft.com/default.aspx?scid=kb;en-us;211580