Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. -- |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. -- |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. -- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to paste multiline HTML into a single cell in Excel 2003? | Excel Discussion (Misc queries) | |||
Textbox and multiline text | Excel Worksheet Functions | |||
import multiline data from text file | Excel Discussion (Misc queries) | |||
Fixing multiline cell | Excel Worksheet Functions | |||
divide multiline text cell across several rows | Excel Programming |