Replace unicode charachters in a text file

[mikke3141]

Hi,

I'm trying to replace Unicode characters that are in a text file, as my vba code concludes that these two characters are line breaks and mess up the file.


Sub TestReplaceTextInFile()
ReplaceTextInFile "C:\Users\BLM31893\Desktop\org.xls", ChrW(&H10D), "b"
ReplaceTextInFile "C:\Users\BLM31893\Desktop\org.xls", ChrW(&H122), "b"
End Sub


Sub ReplaceTextInFile(SourceFile As String, _
sText As String, rText As String)
Dim TargetFile As String, tLine As String, tString As String
Dim p As Integer, i As Long, F1 As Integer, F2 As Integer
TargetFile = "RESULT.TMP"
If Dir(SourceFile) = "" Then Exit Sub
If Dir(TargetFile) <> "" Then
On Error Resume Next
Kill TargetFile
On Error GoTo 0
If Dir(TargetFile) <> "" Then
MsgBox TargetFile & _
" already open, close and delete / rename the file and try again.", _
vbCritical
Exit Sub
End If
End If
F1 = FreeFile
Open SourceFile For Input As F1
F2 = FreeFile
Open TargetFile For Output As F2
i = 1 ' line counter
Application.StatusBar = "Reading data from " & _
TargetFile & " ..."
While Not EOF(F1)
If i Mod 100 = 0 Then Application.StatusBar = _
"Reading line #" & i & " in " & _
TargetFile & " ..."
Line Input #F1, tLine
If sText <> "" Then
ReplaceTextInString tLine, sText, rText
End If
Print #F2, tLine
i = i + 1
Wend
Application.StatusBar = "Closing files ..."
Close F1
Close F2
Kill SourceFile ' delete original file
Name TargetFile As SourceFile ' rename temporary file
Application.StatusBar = False
End Sub


Private Sub ReplaceTextInString(SourceString As String, _
SearchString As String, ReplaceString As String)
Dim p As Integer, NewString As String
Do
p = InStr(p + 1, UCase(SourceString), UCase(SearchString))
If p > 0 Then ' replace SearchString with ReplaceString
NewString = ""
If p > 1 Then NewString = Mid(SourceString, 1, p - 1)
NewString = NewString + ReplaceString
NewString = NewString + Mid(SourceString, _
p + Len(SearchString), Len(SourceString))
p = p + Len(ReplaceString) - 1
SourceString = NewString
End If
If p >= Len(NewString) Then p = 0
Loop Until p = 0
End Sub


After I run the code however the format of the text file is not anymore readable. What should I do? Thank you for your help.

Mike

[mancubus]

hi.

i use below procedure to change the content of a text file.



Sub ReadWriteTextFile()

Dim txtFile As String, txtContent As String

txtFile = "C:\Users\Me\txt_files\example.txt"
Open txtFile For Input As #1
txtContent = Input$(LOF(1), 1)
Close #1

txtContent = Replace(txtContent, ChrW(&H10D), "b")
txtContent = Replace(txtContent, ChrW(&H122), "b")

Open txtFile For Output As #1
Write #1, txtContent
Close #1

End Sub

[snb]

I don't think you solve any problem with this.


as my vba code concludes that these two characters are line breaks and mess up the file.
Which VBA code says what when trying to perform what ?

From your code it seems you are looking for vblf / chr(10) and for G / chr(71)


Sub TestReplaceTextInFile()
ReplaceTextInFile "C:\Users\BLM31893\Desktop\org.xls", ChrW(&H10D), "b"

your argument is an Excel file, not a txt file.

to replace text in a txt file:


Sub M_snb()
c00="G:\OF\example.txt"

with createobject("wscripting.filesystemobject")
.createtextfile(c00).write replace(.opentextfile(c00).readall,"complex","simple")
end with
End sub

[mancubus]

@snb

i looked for that in my 'snb' files before my post.
but couldn't find the file so i posted another solution.

i need to re-train myself on FSO as i seem to forget pretty fast. :banghead:

ps: which W :)

[snb]

@Mancubus

It also took me a while before I could type "scripting.filesystemobject" by heart.

I suppose you are familiar with:


txtContent = replace(Replace(txtContent, ChrW(&H10D), "b"), ChrW(&H122), "b")

[mancubus]

sure.

PS: my PS in previous post was a pointer to another thread with a typo that you reply "which ; ;)" (or similar) just to create a smile. :)

[mancubus]

Sub M_snb()

c00 = "C:\Users\BLM31893\Desktop\org.txt"

With CreateObject("Scripting.FileSystemObject")
.CreateTextFile(c00).Write Replace(Replace(.OpenTextFile(c00).ReadAll, ChrW(&H10D), "b"), ChrW(&H122), "b")
End With

End Sub