remove line break from string variable in VBA

Question

I have declared and assigned a value to a string variable in VBA.

The variable contains a number of line breaks, can you advise how to remove these, this variable has been assigned a value from an xml document using the following code:-

s = Application.GetOpenFilename() 

myFolder = ActiveWorkbook.Path
s = Dir(myFolder & "\*.xml")

Do While s <> ""

If s <> "False" Then

    Set FSO = CreateObject("Scripting.FileSystemObject")
        If FSO.FileExists(s) Then

        t = myFolder & "\" & Replace(FSO.GetTempName(), ".tmp", ".xml")

       Name s As t
        Set ts(0) = FSO.OpenTextFile(t, 1, False, -2) 

        FileContents = ts(0).READALL                

   ts(0).Close
       Set ts(0) = Nothing

   End If
End If

  s = Dir$ 

loop

Can anyone advise a solution?

Thanks Nick

Answer 1

First, you need to determine which line break your variable has - or you can test for all of them using multiple replaces. Luckily, VBA has the vbConstants for line breaks which make your life a little easier:

myStr = Replace(myStr, vbCr, " ")
myStr = Replace(myStr, vbLf, " ")
myStr = Replace(myStr, vbCrLf, " ") '// or vbNewLine

Notice I've used a space as the replacement otherwise you will end up with words being merged:

Example of some text
with a line break

replacing the line break with a zero length string "" would result in:

Example of some textwith a line break

whereas replacing with a space " " will produce:

Example of some text with a line break

---

Just for fun another way of doing this is like so:

myStr = Join$(Split(myStr, vbCrLf), " ")

This uses the line break as a delimiter to split the string out into a single dimension array, then joins each element of the array with a space. No real advantage in either method just down to preference in this case.