Open Notepad and Find/Replace

[Eugene-LS]

there's a lot of Russian comments

Although... fu...k it all!
Whoever wants it will understand - or ask me.
...
See to "mod00Main" module - Private Sub TestTXTfile() ...

Forgive my rudeness - I studied English in the Pubs of the good port city of Hull (Kingston upon Hull)

 

[theDBguy]

The problem is that if I try to edit the text file using VBA it see this character and thinks it's at EOF and exits.

Perhaps if you could tell us what kind of edit you are trying to do with the text file using VBA, we might be able to give you a better solution. If you just need to read the text file into a variable, then maybe you can take a look at this blog article.

How to Read a Text File

Invariably, at one point or another in your journey to write VBA in Access, there will come a time when you will need to read the contents o...

thedbguy.blogspot.com

 

[D_Walla]

The following seems to strip the offending characters from the file. Does this work for you? You should only need to update the file path in the principal subroutine, CleanTextFile.

Spoiler: I assume I need to mark the code as a spoiler?

Sub CleanTextFile()

    Dim Txt         As String
    Dim Filename    As String
    Dim NewFilename As String
   
    Filename = "C:\PATHTOFILE\Test.txt"
    Txt = ReadFile(Filename)
    Txt = Replace(Replace(Txt, Chr(vbNull), ""), Chr(26), "")
    NewFilename = Replace(Filename, ".txt", "_FIXED.txt")
    CreateFile NewFilename, Txt

End Sub

Sub CreateFile(ByVal Filename As String, ByVal Contents As String)
   
    Dim FileHandled As Long
    FileHandled = FreeFile
   
    Open Filename For Output As #FileHandled
        Print #FileHandled, Contents
    Close #FileHandled
End Sub
Function ReadFile(ByVal Filename As String) As String
   
    Dim EOFile      As Long
    Dim FileHandled As Long
    Dim Contents    As String
   
    FileHandled = FreeFile
   
    Open Filename For Binary As #FileHandled
        EOFile = LOF(FileHandled)
        Contents = Space$(EOFile)
        Get #FileHandled, , Contents
    Close #FileHandled
   
    ReadFile = Contents
End Function

 

[KitaYama]

I'm waiting for your approval

I think you have misunderstood me with the OP.
I have no problem. The file or the question is not mine. So my approval is meaningless.
I just wanted to see how you guys solve OP's problem.

 

[theDBguy]

Spoiler: I assume I need to mark the code as a spoiler?

Actually, no, you don't have to do that. Just FYI...

 

[D_Walla]

Actually, no, you don't have to do that. Just FYI...

Thank you! I saw that Eugene-LS had done it, so I followed suit. I easily succumb to peer-pressure.

 

[arnelgp]

and another demo to remove unwanted characters.

 

[Mike Krailo]

Thanks for that Arnel, that took what I was doing to the next level and whether or not the OP needs it, I certainly find your demo very useful as it can strip all the control characters out of the file from anywhere. Allowed control characters are BS, Line Feed, and Carriage Return.

Edit: Darn, I see now that the replacement character cannot be vbNullString which is unfortunate since I'm guessing that is more in line with what the OP was trying to do. I put an extra step in Arnel's routine to handle that by first converting those unwanted characters to a unique character (a space won't work), then doing a standard replace on it. Now it functions the way I would expect it to.