Displaying multiline Text into Excel Cell through Interop
 

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

Displaying multiline Text into Excel Cell through Interop
 

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


"Karthikeyan" wrote:

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

Displaying multiline Text into Excel Cell through Interop
 

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


keepITcool wrote:

"Karthikeyan" wrote:

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

Displaying multiline Text into Excel Cell through Interop
 

keepITcool wrote ...

"Karthikeyan" wrote:

I have data in SQL server

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.

--

Displaying multiline Text into Excel Cell through Interop
 

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


(Jamie Collins) wrote:

keepITcool wrote ...

"Karthikeyan" wrote:

I have data in SQL server

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.

--

Displaying multiline Text into Excel Cell through Interop
 

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.

--