change CR to LF (vba 97)

Topic

New Reply

Hello all,

I have a TXT file where I have to replace all CR to LF.
Is it posible to use some code in an Office application to change this txt file?

Thanks in advance.

Reply | Quote

Replies

Here is a general VBA routine to do so. It can be run from Word, Excel or Access, or incorporated in a VB6 application. For testing purposes, the code will create a new file; test thorougly that the output is correct. When satisfied, you can uncomment two lines near the end (indicated in the code) to make the code replace the original file.

Sub Cr2Lf(strFilename As String)
Dim intIn As Integer
Dim intOut As Integer
Dim bytChar As Byte

On Error GoTo ErrHandler

intIn = FreeFile
Open strFilename For Binary Access Read As #intIn
intOut = FreeFile
Open strFilename & “2” For Binary Access Write As #intOut

Do While Not EOF(intIn)
Get #intIn, , bytChar
If bytChar = 13 Then bytChar = 10
Put #intOut, , bytChar
Loop

Close #intOut
Close #intIn
‘ Uncomment the following lines after testing
‘Kill strFilename
‘Name strFilename & “2” As strFilename

ExitHandler:
On Error Resume Next
Close #intOut
Close #intIn
Exit Sub

ErrHandler:
MsgBox Err.Description, vbExclamation
Resume ExitHandler
End Sub

Use as follows:

Cr2Lf “C:TestTextfile.txt”

Reply | Quote

Just curious here Hans. Is it possible to use Word’s search/ replace dialog to do this? That is, use ^0013 and ^0010 in the S/R dialog?

Alan

Reply | Quote

Seems to work

Don’t know if there are situations in which Word would mess up the file, but it looks OK in a quick test.

Reply | Quote

Seems to work

Don’t know if there are situations in which Word would mess up the file, but it looks OK in a quick test.

Reply | Quote

Word can’t deal properly with ^10. You might be lucky if you immediately save in *.txt format again, but usually Word will replace ^10 (or ^13^10 = CrLf pairs) with ^13.

Probably the other methods mentioned are safer.

BTW: In Word2002/2003, there is an option when you save as text that allows you to end lines with ^13^10, ^13, ^10, or ^10^13.
In Word97/2000, there was a file MSTXTCNV.INI, which allowed you to set a few options for how Word saved files in certain formats. But I don’t remember anything about end-of-line characters in text files.

Klaus

Reply | Quote

Thanks (Hans too) for the reminder of Word “doing its own thing” in certain capacities. I should have remembered how I used to use Word to reformat certain text files, simply by opening then resaving. I’d concur that the CRLF issue is a bit of a wildcard with Word.

Alan

Reply | Quote

Thanks (Hans too) for the reminder of Word “doing its own thing” in certain capacities. I should have remembered how I used to use Word to reformat certain text files, simply by opening then resaving. I’d concur that the CRLF issue is a bit of a wildcard with Word.

Alan

Reply | Quote

Word can’t deal properly with ^10. You might be lucky if you immediately save in *.txt format again, but usually Word will replace ^10 (or ^13^10 = CrLf pairs) with ^13.

Probably the other methods mentioned are safer.

BTW: In Word2002/2003, there is an option when you save as text that allows you to end lines with ^13^10, ^13, ^10, or ^10^13.
In Word97/2000, there was a file MSTXTCNV.INI, which allowed you to set a few options for how Word saved files in certain formats. But I don’t remember anything about end-of-line characters in text files.

Klaus

Reply | Quote

Just curious here Hans. Is it possible to use Word’s search/ replace dialog to do this? That is, use ^0013 and ^0010 in the S/R dialog?

Alan

Reply | Quote

Here is a general VBA routine to do so. It can be run from Word, Excel or Access, or incorporated in a VB6 application. For testing purposes, the code will create a new file; test thorougly that the output is correct. When satisfied, you can uncomment two lines near the end (indicated in the code) to make the code replace the original file.

Sub Cr2Lf(strFilename As String)
Dim intIn As Integer
Dim intOut As Integer
Dim bytChar As Byte

On Error GoTo ErrHandler

intIn = FreeFile
Open strFilename For Binary Access Read As #intIn
intOut = FreeFile
Open strFilename & “2” For Binary Access Write As #intOut

Do While Not EOF(intIn)
Get #intIn, , bytChar
If bytChar = 13 Then bytChar = 10
Put #intOut, , bytChar
Loop

Close #intOut
Close #intIn
‘ Uncomment the following lines after testing
‘Kill strFilename
‘Name strFilename & “2” As strFilename

ExitHandler:
On Error Resume Next
Close #intOut
Close #intIn
Exit Sub

ErrHandler:
MsgBox Err.Description, vbExclamation
Resume ExitHandler
End Sub

Use as follows:

Cr2Lf “C:TestTextfile.txt”

Reply | Quote

Another way would be to use VBScript from the command prompt. A demo is attached. You can use the VB Shell object to run batch files, but I’m not sure you could pass the file name parameter.

Reply | Quote

Another way would be to use VBScript from the command prompt. A demo is attached. You can use the VB Shell object to run batch files, but I’m not sure you could pass the file name parameter.

Reply | Quote