Solved: Find and replace end of file character in Notepad using VBA

[fransajp]

Hi

I need VBA code to remove a end of file character from a Notepad file please.

I've tried using the code in Open notepad, find and replace text with the filename, but VBA sees the end of file character as the end of the document and thus the code breaks("Input past end of file").

Example:
Name Age
Len 19
Frans 27

If the document contains the above, how could I replace the  with a space?

Thank you

[Bob Phillips]

Have you tried using the text methods?

[fransajp]

Sorry I don't know what are the text methods? I'm self tought in VBA and my knowledge is a bit limited.

[Bob Phillips]

You would have to find out what that character is, but som ething like

[vba]

Dim fnFile2 As Long
Dim mData As Variant

fnFile1 = FreeFile
Open "C:\Mytext.txt" For Input As fnFile1
fnFile2 = FreeFile
Open "C:\MyNewtext.txt" For Output As fnFile2
Do While Not EOF(fnFile1)

Line Input #fnFile1, mData
mData = Replace(mData, "~", "")
Print #fnFile2, mData
Loop

Close fnFile1
Close fnFile2
[/vba]

[fransajp]

If I run the code below I get the following result:

Name Age
Len

The character is some kind of end of file character, so VBA thinks it is where the document ends, even though there are more data after the character.

[Bob Phillips]

You need to find out what that character is so as to replace it.

[fixo]

Hi

I need VBA code to remove a end of file character from a Notepad file please.

I've tried using the code in Open notepad, find and replace text with the filename, but VBA sees the end of file character as the end of the document and thus the code breaks("Input past end of file").

Example:
Name Age
Len 19
Frans 27

If the document contains the above, how could I replace the  with a space?

Thank you

Try this code, change file names to suit

Sub Demo()
Dim search As String
search = ""
Dim repl As String
repl = " "
Dim text As String
Dim arr As Variant
Dim line As String
Dim item As Variant
    Open "C:\tmp1.txt" For Binary Access Read Lock Read As #1
    text = Input$(LOF(1), #1)
    Close #1
 
    arr = Split(text, vbNewLine)
 
    Open "C:\tmp2.txt" For Output As #2 '<--temporary file
 
    For Each item In arr
 
    line = Replace(CStr(item), search, repl, 1, -1)
    Print #2, line
 
    Next
 
    Close #2
 
    Kill "C:\tmp1.txt"
    Name "C:\tmp2.txt" As "C:\tmp1.txt"
 
End Sub

~'J'~

[fransajp]

Thank you Fixo, that works perfectly!