delete blank lines in excel cell

delete blank lines in excel cell - Printable Version

+- Micro Focus QTP (UFT) Forums (https://www.learnqtp.com/forums)
+-- Forum: Micro Focus UFT (earlier known as QTP) (https://www.learnqtp.com/forums/Forum-Micro-Focus-UFT-earlier-known-as-QTP)
+--- Forum: UFT / QTP Beginners (https://www.learnqtp.com/forums/Forum-UFT-QTP-Beginners)
+--- Thread: delete blank lines in excel cell (/Thread-delete-blank-lines-in-excel-cell)

delete blank lines in excel cell - QAVA - 12-06-2012

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

RE: delete blank lines in excel cell - diya - 12-07-2012

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

RE: delete blank lines in excel cell - elango87 - 12-07-2012

@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