Displaying multiline Text into Excel Cell through Interop

[Karthikeyan]

Hi,
The scenario is, I have data in SQL server, which needs
to be populated in Excel Sheet through interop. The data
in SQL Server contains new line characters also. I need
to display the text as multiline text into the Excel
Cell. When we just assign the text, the excel engine
renders the new line characters as special characters
(weired character like a square box). Any Solutions..

Thanks in Advance
Karthik

 

[keepITcool]

A quick test shows SQL returning a CrLf
chr$(13)&chr$(10)

Excel wrapped cells use just an LF chr(10).

You could do THIS after you've inserted the query...

Cells.Replace What:="" & Chr(10) & "", Replacement:="", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False




alternatively you could use Replace function in your query:

SELECT Replace([Table1]![textwrapped],Chr(10),"") AS ExcelWrapped
FROM Table1;





keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >

 

[keepITcool]

ouch... wrong way round => you must replace 13 not the 10

Cells.Replace What:="" & Chr(13) & "", Replacement:="", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False


SELECT Replace([Table1]![textwrapped],Chr(13),"") AS ExcelWrapped
FROM Table1;



keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >

 

[Jamie Collins]

you could use Replace function in your query:

SELECT Replace([Table1]![textwrapped],Chr(10),"") AS ExcelWrapped
FROM Table1;

I tried this and got an error, Incorrect syntax near '!'.

Jamie.

--

 

[keepITcool]

hmm..

it works in Access2002/2003...
and (the fatal words).. I assumed...
would work in SQL server.

cant test in sql server and need to reinstall MSDE
but replace IS listed in TSQL reference.


MSQUERY from Excel doesn't accept the replace function,
and cant get it to work with ADO either...

So we've got multiple problems here..
brackets vs backquotes

SELECT Replace([Table1]![textwrapped],Chr(13),"") AS Expr1
FROM Table1;
SELECT Replace(`Table1`!`textwrapped`,Chr(13),"") AS Expr1
FROM Table1;
SELECT Replace(Table1!textwrapped,Chr(13),"") AS Expr1
FROM Table1;

Apparently there's some controversy on the replace function
could a combination of STUFF and INSTR be made to work?


Jamie can you help here.. ?
I think you know more about sql than I do



keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >

you could use Replace function in your query:

SELECT Replace([Table1]![textwrapped],Chr(10),"") AS ExcelWrapped
FROM Table1;

I tried this and got an error, Incorrect syntax near '!'.

Jamie.

--

 

[Jamie Collins]

keepITcool wrote ...

Jamie can you help here.. ?
I think you know more about sql than I do

Flattery works for me <g>, as does this with SQL Server 2000 (ADO and
Query Analyzer):

SELECT REPLACE(textwrapped, CHAR(13), '') AS Expr1
FROM Table1;

Jamie.

--