delete blank lines in excel cell

[elango87]

@Diya,

You have type the first line in the cell and press Shift+enter to get to the next line.

@QAVA
I broke my head for the past two days trying to find a solution.
The newline character in excel is recognized as some special character. I tried using split function which dint help.It goes something like this,

Code:

Str = Split(CellValue, vbnewline)

I did try many other things but nothing helped.
Finally i wrote the contents of the cell in to a text file and tried reading from it.
I am half successful in doing it.

I was able to remove the spaces between the lines but at the end of the line, some special character it getting added. I am not able to fix it.

Here is the code... hope someone will be able to fix it...
---

Code:

Dim oExcel, oWbook, oSheet

Set oExcel = CreateObject("Excel.Application")
Set oWbook = oExcel.Workbooks.Open(RelPath&"\Test.xls")
Set oSheet = oWbook.Worksheets("Sheet1")
oExcel.Visible = True

Dim CellValue

CellValue = oExcel.Cells(1,1).Value

Set objfso = CreateObject("Scripting.FileSystemObject")
objfso.CreateTextFile(RelPath&"\Test.txt")
Set objfile = objfso.OpenTextFile(RelPath&"\Test.txt",2)
objfile.WriteLine(CellValue)
objfile.Close()
Set objfile = objfso.OpenTextFile(RelPath&"\Test.txt",1)
Dim Str,Str1
Do Until objfile.AtEndOfStream
    Str1 = Trim(objfile.ReadLine)
    If Str1 <> "" Then
        Str = Str & Str1 & vbnewline
    End If
Loop

Print Str
oExcel.Cells(1,2).Value = Str

---

Hope this will help you.

Thanks,
Elango