Odd behaviour in CRLF Code using cells with multi-line text

[Rhudi]

I have a minor annoyance when I create text files using a macro.

I am using normal code to open text files, then using 'Print' to write the data.

What happens is when the cell contains text that uses "Shift-Enter" to get multiple lines of text within a cell. When I print that cell, the lines of text end with vbLF. The 'print' itself ends with vbCRLF.

During my 'print', how can I make every line always end with vbCRLF?

Currently, I'm using NotePad++ to "fix" the file. I open the file, do Ctrl-A, Ctrl-X, Ctrl-V, Ctrl-S, Ctrl-W. Open the next file. Doesn't sound like a big deal, but doing 100 or more files this manual way seems silly.

I'd love to know how to make the 'Print' command just do the vbCRLF even for text it's printing that only had vbLF to begin with.

I hope this isn't a really stoopid question.

[shg]

Replace vbCrLf with Chr(143), then replace vbLf with vbCrLf, then replace Chr(143) with vbCrLf, then print.

[Rhudi]

Within the cell of multi-line text?

[shg]

Applied to the string that's in the cell before you write it to the file.

[Rhudi]

I wrote a short test. I did something wrong. I tried it a few different ways, leaving in the commented lines in and out, then picking one cell to single step through. The text on the screen in excel gets these little boxes on every line. However, the 'Print' command still leaves me with output that has vbLF on each of the multiple lines from the source text. It's almost as if the 'Print' command itself writes out the vbLF.

Sub ReplaceLfs()

Dim pobjCell As Range
Dim plCharCounter As Long
Dim psCellText As String
' Replace vbCrLf with Chr(143), then replace vbLf with vbCrLf, then replace Chr(143) with vbCrLf, then print.
    
    Application.ScreenUpdating = False

    For Each pobjCell In Selection
        psCellText = pobjCell.Text
        'Do While InStr(psCellText, vbCrLf) > 0
            psCellText = Replace$(psCellText, vbCrLf, Chr(143))
            psCellText = Replace$(psCellText, vbLf, vbCrLf)
            psCellText = Replace$(psCellText, Chr(143), vbCrLf)
        'Loop
        pobjCell.Value = psCellText
    Next

    Application.ScreenUpdating = True

End Sub

So, my test is to type into a cell of text, using Alt-Enter to make multiple lines in the cell. Then Open a file for output and 'Print' to that file. The output has vbLF. If I force the 'Replace$' using the code, I can make the cell get these little boxes, that do not affect the 'Print' output in any way.

However, if I copy the cell and paste it into Notepad++, I do get CRLF on every line.

[shg]

I've lost the bubble. I thought you were outputting to a file -- there is nothing to do that in your code.

[Rhudi]

I am using 'Print' to put my data into a text file. I was just trying to show the steps I've been doing. The code listed was to try to do to a cell what you said, then use my other code to write the file, using that updated cell. It appears that the actual 'Print #1, Range("A1") & vbCrLf' does not care what each line has at the end. the text file I get, just has LF on the lines from the cell, and the extra vbCrLf appears as such in the text.

[shg]

This does exactly what I would expect:

Sub x()
    Dim sOut        As String
    Dim i           As Long

    Range("A1").Value = "Now is the time " & vbLf & _
                        "for all good men"
    Range("A2").Value = "to come to the aid" & vbLf & _
                        "of their country"

    Open "C:\Users\shg\Desktop\Test.txt" For Output As #1

    For i = 1 To 2       
        sOut = Replace(Replace(Replace(Cells(i, "A").Value, vbCrLf, Chr(143)), vbLf, vbCrLf), Chr(143), vbCrLf)
        Print #1, sOut
    Next i

    Close #1
End Sub

In what way is it different that what you expect?

[Rhudi]

Well, obviously when someone with real brains does it the correct way, compared to my semi-intelligent attempts, you get real results.
Doing it your way, fixes it.
I used to do this:

Print #1, Sheets(Rtr + 2).Range("P" & R).Value

From your excellent help, this works:

sOut = Replace(Replace(Replace(Sheets(Rtr + 2).Range("P" & R).Value, vbCrLf, Chr(143)), vbLf, vbCrLf), Chr(143), vbCrLf)
Print #1, sOut

I have to use .Range, instead of .Cell - I'm sure you can explain why.
Now, I do it this way:

Print #1, Replace(Replace(Replace(Sheets(Rtr + 2).Range("P" & R).Value, vbCrLf, Chr(143)), vbLf, vbCrLf), Chr(143), vbCrLf)

But, you're smarter than me. Thank you.

[shg]

Range("P" & R).Value is the same as Cells(R, "P")

[Rhudi]

Oh! See what I mean about real brains vs mine?