delete blank lines in excel cell

[QAVA]

Excel cell(1,2) has data with blank lines as:
"
vb is hard language.

test data.

test vb.
"

I want to change to without blank lines as "

"vb is hard language.
test data
test vb"

Below code is not working. Please please help.

----------

Code:

Set ExcelApp = createobject("Excel.Application")
ExcelApp.Visible = True  
Dim FilePath
FilePath = "F:\afi\vbscript\Test.xls"
Set ExcFile = ExcelApp.Workbooks.Open (FilePath)
a = ExcelApp.Cells(1,2)
b = Replace(a , "   " , "")
ExcelApp.Cells(1,2) = b

[diya]

Code:

Set ExcelApp = createobject("Excel.Application")
ExcelApp.Visible = True  
Dim FilePath
FilePath = "F:\afi\vbscript\Test.xls"
ExcelApp.Workbooks.Open (FilePath)
set newsheet=ExcelApp.Sheets.Item(1)
a = newsheet.Cells(1,2)
b = Replace(a , "   " , "")
newsheet.Cells(1,2) = b
msgbox b

i dont if this works coz iam not able to create a excel with blank values..try this if not sorry

[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