topic RE: Replace break Line in Excel in Product Forum

Replace break Line in Excel

MateuszWojcik — Tue, 02 Jul 2019 12:30:00 GMT

Hello,

I want to create object which will replace charecters using ASCII Characters as arguments. I need this object to delete all line breaks in excel file ( Chr(10)).
My code stage does not work when I use ASCII characters:
range.Replace(Chr(115), Chr(100))

It works when I use strings:
range.Replace("s", "d")

Do you know how to create object to replace values using ASCII characters or how to delete all line breaks in excel sheet?

Chr(115) = "s"
Chr(100) = "d"

THE WHOLE CODE
Dim wb, excel, range As ObjectDim wb, excel, range As Object

Try

wb = GetWorkbook(Handle, Workbook)
excel = wb.Application
range = excel.Selection

'range.Replace("s", "d")
range.Replace(Chr(115), Chr(100))

Success = True
Catch e As Exception
Success = False
Message = e.Message

Finally
wb = Nothing
excel = Nothing
range = Nothing

End Try

Thank you for your help

------------------------------
Mateusz Wojcik
RPA Programist
Rockwell Automation
Europe/Warsaw
------------------------------

RE: Replace break Line in Excel

GopalBhaire — Wed, 03 Jul 2019 06:24:00 GMT

Try this

range.Replace(vbLf, "")

------------------------------
Gopal Bhaire
Analyst
Accenture
------------------------------

RE: Replace break Line in Excel

MateuszWojcik — Wed, 03 Jul 2019 07:16:00 GMT

Thank you it is working.
Break line is replace.

Do you know why ASCII Characters are not working for replace function ?

------------------------------
Mateusz Wojcik
RPA Programist
Rockwell Automation
Europe/Warsaw
------------------------------