Delete blank lines in excel cell using vbscript

[bangla]

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.

----------
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

[TnTinMN]

Looks like VB6 to me or possibly Shell Scripting. You are in the VB.Net forum.

Your blank lines are likely due to to VBNewline characters.

I can't test this, but give it a try.

Instead of using "Replace" use the "Split" function to create an array of strings; split on VBNewline. Then use the "Filter" function with the filter set to VBNewline and the "switch" parameter set to False to create an array with the blank lines removed. Then create a new string by concatonating each array element. Remember to add a VBNewline to the end of each element.

Good luck.

[bangla]

Thanks. I will give a try.

[bangla]

I guess I am lost. Could you please break it down little more.

[TnTinMN]

I guess they updated VBA a bit since I last did much work with it so I was able to test this out. My mistake on the filter Function, I forgot it ignores empty strings. This should work for you.

Set excelApp = CreateObject("Excel.Application")
excelApp.Visible = True
Dim FilePath As String
FilePath = "F:\afi\vbscript\Test.xls"
Set ExcFile = excelApp.Workbooks.Open(FilePath)
Dim a As String
a = excelApp.Cells(1, 2).Value
Dim parts() As String
parts = Split(a, vbNewLine)

Dim b As String
b = ""
Dim ubparts As Integer
ubparts = UBound(parts)
Dim i As Integer
For i = 0 To ubparts
    If parts(i) <> vbNullString Then

        b = b & IIf(i > 0 And Len(b) > 0, vbNewLine, vbNullString) & parts(i)
    End If
Next i
excelApp.Cells(1, 2).Value = b

[bangla]

Thanks.
I am running the code in notepad.vbs. 
I modified all Dim lines because it is giving me error as " Dim as .....". 
I have the below code. After i run script, it opens the excel, then get an error at line 9. says "type mismatch"

line 9:   parts = Split(a, vbNewLine)

-----------------
Set excelApp = CreateObject("Excel.Application")
excelApp.Visible = True
Dim FilePath 
FilePath = "F:\afi\vbscript\Test.xls"
Set ExcFile = excelApp.Workbooks.Open(FilePath)
Dim a
a = excelApp.Cells(1, 2).Value
Dim parts() 
parts = Split(a, vbNewLine)
Dim b 
b = ""
Dim ubparts 
ubparts = UBound(parts)
Dim i 
For i = 0 To ubparts
   If parts(i) <> vbNullString Then
      b = b & IIf(i > 0 And Len(b) > 0, vbNewLine, vbNullString) & parts(i)
    End If
Next 
excelApp.Cells(1, 2).Value = b

[TnTinMN]

I don't know what notepad.vbs is but I suspect it is some type of shell scripting host. You probably will get better support in this forum

Click Here

A guess whould be that the value of "a" is empty at that point. Check your Exel file. You could enclose everything after the "a=" statement in an if block.

If Not (IsNull(a) Or IsEmpty(a)) Then
' the statements

End If

[bangla]

I simply open the notepad enter code then save it with .vbs extention.

Then double click the vbs file to run it.

---

I have data in "a". i had 'msgbox a' which displays data.

i get error here in 17. it says type mismatch. for some reason it is not storing into the array parts() after splitting.

16.Dim parts()
17.parts = Split(a, vbNewLine)

Please advise.

[bangla]

If i say as below, i get error--type mismatch.

16.Dim parts()
17.parts = Split(a, vbNewLine)

If i say as below, i get error- subscript out of range.

16.Dim parts()
17.parts() = Split(a, vbNewLine)

[TnTinMN]

five this a try

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

If Not (IsNull(a) Or IsEmpty(a)) Then
    dim parts
    parts = Split (a, VBLF)

    Dim b
    b = ""

    for each p in parts
        If p <> vbNullString Then
            select case Len(b)
            case 0
                b = b &  p
            case else
                b = b & vbLF & p
            end select
        End If  

    next

    excelApp.Cells(1, 2).Value = b
Else
    MsgBox ("null cell")
End If
excelapp.Quit()

[bangla]

That did a lot for me.

I am so thankful. I spent lot of time. I could not do it. Many many thanks.