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