Stop export to excel truncating long text at 255 chars...

[GriffMG]

I started this as a question, because I added an excel export option to a report using automation to create a spreadsheet from a table that had memo fields.
The user got back to me saying the memos were truncated to 255 characters (not EXACTLY what he said, but you get my drift).

The text was often quite a bit longer than that, and I searched on here for a solution, couldn't quite find it. Then I looked at a similar thing I had done years ago
and found that if I created the excel using COPY TO ... XL5 and then tried to use automation to add the missing memo fields, they were truncated.

But, if I simply created a new spreadsheet using automation, then put all the fields in (including the memos), it was perfect.

No idea why:

	OEXCEL = CREATEOBJECT("Excel.Application")
	OEXCEL.VISIBLE = .F.

	OEXCEL.WORKBOOKS.ADD
	OEXCEL.DISPLAYALERTS = .F.

	OEXCEL.WORKSHEETS("Sheet3").DELETE
	OEXCEL.WORKSHEETS("Sheet2").DELETE
	OEXCEL.DISPLAYALERTS = .T.
	OEXCEL.CELLS.SELECT
	OEXCEL.SELECTION.COLUMNWIDTH = 12
	OEXCEL.SELECTION.FONT.NAME = "Arial"
	OEXCEL.SELECTION.FONT.SIZE = 8
	SELECT TMPTQ
	GO TOP
	I = 2
	OEXCEL.CELLS(1,1).VALUE = "Origin"
	OEXCEL.CELLS(1,2).VALUE = "QueryNo"
	OEXCEL.CELLS(1,3).VALUE = "MCI No"
	OEXCEL.CELLS(1,4).VALUE = "Details"
	OEXCEL.CELLS(1,5).VALUE = "Date Recd"
	OEXCEL.CELLS(1,6).VALUE = "Date Issued"
	OEXCEL.CELLS(1,7).VALUE = "Date required"
	OEXCEL.CELLS(1,8).VALUE = "Date Recived Back"
	OEXCEL.CELLS(1,9).VALUE = "Date returned to Origin"
	OEXCEL.CELLS(1,10).VALUE = "Answer"
	DO WHILE .NOT. EOF()
		OEXCEL.CELLS(I,1) = TMPTQ.MADEBY
		OEXCEL.CELLS(I,2) = TMPTQ.QUERYNO
		OEXCEL.CELLS(I,3) = TMPTQ.MCINO
		OEXCEL.CELLS(I,4) = TMPTQ.DETAILS
		
		OEXCEL.CELLS(I,5).VALUE = DTOL(TMPTQ.DATERECD)
		OEXCEL.CELLS(I,6).VALUE = DTOL(TMPTQ.ISSUDATE)
		OEXCEL.CELLS(I,7).VALUE = DTOL(TMPTQ.RETNDATE)
		OEXCEL.CELLS(I,8).VALUE = DTOL(TMPTQ.RETEDATE)
		OEXCEL.CELLS(I,9).VALUE = DTOL(TMPTQ.RETURNNDATE)
		OEXCEL.CELLS(I,10) = TMPTQ.FURTHER
		I = I +1
		SKIP
	ENDDO

	OEXCEL.CELLS.SELECT
	OEXCEL.CELLS.VERTICALALIGNMENT= -4160
	OEXCEL.SELECTION.ROWS.AUTOFIT
	OEXCEL.SELECTION.COLUMNS.AUTOFIT
	OEXCEL.RANGE("A2").SELECT
	OEXCEL.DISPLAYALERTS = .F.
	OEXCEL.ACTIVEWORKBOOK.SAVEAS("c:\temp\myfile.XLS")
	OEXCEL.QUIT
	RELEASE OEXCEL

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are not good for you. ​

 

[vgulielmus]

Maybe depends on Excel version

This small demo does not truncate to 255, using Excel 2010

CLOSE DATABASES ALL
CREATE TABLE cc (cc C(20),mm M,mm2 M)
INSERT INTO cc VALUES ('Abcd', REPLICATE('Abcd Efghi ',50), REPLICATE('Abcd Efghi ',50))
INSERT INTO cc VALUES ('Uvw', REPLICATE('Uwv Xyzpqrs ',50), REPLICATE('Uwv Xyzpqrs ',50))
INSERT INTO cc VALUES ('Ijkl', REPLICATE('Ijkl Mnopqrst ',50), REPLICATE('Ijkl Mnopqrst ',50))
GO TOP 
COPY TO d:\kit\zz.xls XL5 

OEXCEL = CREATEOBJECT("Excel.Application")
OEXCEL.VISIBLE = .T.
OEXCEL.WORKBOOKS.open('d:\kit\zz.xls')
SCAN
*	OEXCEL.CELLS(RECNO('cc'),1) = cc.cc
	OEXCEL.CELLS(RECNO('cc')+1,2) = cc.mm
	OEXCEL.CELLS(RECNO('cc')+1,3).Formula = "=LEN(B" + TRANSFORM(RECNO('cc')+1)+")"
	OEXCEL.CELLS(RECNO('cc')+1,4) = cc.mm2
	OEXCEL.CELLS(RECNO('cc')+1,5).Formula = "=LEN(D" + TRANSFORM(RECNO('cc')+1)+")"
ENDSCAN

CLOSE DATABASES ALL

Respectfully,
Vilhelm-Ion Praisach
Resita, Romania

 

[GriffMG]

I was using both Excel 2000 and 2010

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are not good for you. ​

 

[Olaf Doschke]

COPY TO of course truncates, the resulting excel sheet may have the column defined as width 255 chars text, excel column can have such formats defined, then altering it with the full memo doesn't work. Working directly with automation of a new blank sheet is best in respect of not having such formats in your way.

You have a solution now, another one would be going through CSV via my faq184-7770

Bye, Olaf.

 

[GriffMG]

I think you may be right Olaf

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are not good for you. ​