Excel VBA find/replace text in .txt file

Question

I have near zero experience with VBA so bear with me here.

I'm trying to create a macro that open a text file and find the text located in Excel's cell A1, and to replace it with text in cell B1. Then, it should find the text located in cell A2, and replace it with cell B2, and so on until the last cell in column A that contains data.

Now, I've searched a bit and stumbled upon this working code:

Sub Replace_Text()
Dim strFile As String
Dim i As Integer
Dim strText As String
Dim cell As Range
With Application.FileDialog(msoFileDialogFilePicker)
    .InitialFileName = ThisWorkbook.Path
    If .Show <> -1 Then Exit Sub
    strFile = .SelectedItems(1)
End With
i = FreeFile
strText = Space(FileLen(strFile))
With CreateObject("vbscript.regexp")
    .Global = True
    Open strFile For Binary Access Read Write As #i
        Get #i, , strText
        For Each cell In Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)
            .Pattern = Replace(Replace(Replace(Replace(cell.Value, "?", "\?"), "*", "\*"), "+", "\+"), ".", "\.")
            strText = .Replace(strText, cell.Offset(, 1).Value)
        Next cell
        Put #i, 1, strText
    Close #i
End With     
End Sub

It's working exactly as intended, except for 1 minor problem. It seems to copy the last few characters in the text file and append it after the last character, making some duplication.

Example:

Column A | Column B
<Var1>   | Patrick
<Var2>   | ghosts

Before running code:

This is <Var1>.
There are <Var2>.
Some random text

After running code:

This is Patrick.
There are ghosts.
Some random textom text

The last few characters "om text" got duplicated and output as such. Sometimes more characters got duplicated depending on the file size. How do I fix this?

What happens if you don't use RegEx and just use VBA - Replace? — Zac
– Zac, Commented Feb 25, 2019 at 13:46
When I do these sort of things I usually just import the .txt file as a new worksheet, and then save as .txt afterwards again. — Luuklag
– Luuklag, Commented Feb 25, 2019 at 13:48

FunThomas · Accepted Answer · 2019-02-25 14:13:49Z

2

Probably, this happens when the output string is shorter than the input. You are opening the file for read and write, read the text (let's say 100 bytes), do your replaces (let's say 90 bytes). Then you write the 90 bytes at the beginning. The remaining 10 bytes in the file stay untouched.

You should open the file first just for read (and close it), then open it again to write the text into it - the old content will be thrown away when you open the file for Output

    Open strFile For Binary Access Read Write As #i
    Get #i, , strText
    Close #i

    For Each cell In Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)
       ...
    Next cell

    Open strFile For Output As #i
    Write #i, strText
    Close #i

answered Feb 25, 2019 at 14:13

FunThomas

31.3k4 gold badges24 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Tin Bum Over a year ago

Interesting, I was wondering why the line "strText = Space(FileLen(strFile))" was there, clearly an attempt to address the problem you mention

FunThomas Over a year ago

@user1302114: No, this is to "allocate" a string that is exactly as long as the file so that the get reads the whole content at once.

Collectives™ on Stack Overflow

Excel VBA find/replace text in .txt file

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related