search and replace text files based on list in excel [solved]

[ian762]

I am looking for a way to search and replace text files based on a list in excel.

i.e. I want to search text fileA - for the text string in worksheet1, column A and replace it with the text string in worksheet1, column B.

So I would search an entire folder of text files for the text in cell A1 and replace with the text in cell B1, then the same for A2 and B2, etc.

[GeneralDisarray]

Open the attached file, on Sheet1 - put your A and B list in place.

Add the PATH to the files (to the folder they sit in) in cell E1.

Then you can press the button to run the bulk find-n-replace operation on all the file. Below is the code that I ran.

(note: if you try putting the code into you own workbook, you'll need to edit it for the ranges I've highlighted. Probably easier to use this file).

Option Explicit

Sub FNR_TextFiles()
Dim C As Range
Dim contents As String, fName As String

Dim FSO As FileSystemObject
Dim FLD As Folder
Dim FIL As File, F2 As File
Dim ts As TextStream

Set FSO = New FileSystemObject
Set FLD = FSO.GetFolder(Sheet1.[pathToFiles].Value)


For Each FIL In FLD.Files
    If UCase(FIL.Name) Like "*.TXT" Then
        Set ts = FIL.OpenAsTextStream
        
        contents = ts.ReadAll
        For Each C In Sheet1.[FINDS].Cells
            contents = Replace(contents, C.Value, C.Offset(0, 1).Value)
        Next C
        fName = FIL.Path
        ts.Close
        
        Set ts = FSO.CreateTextFile(Filename:=fName, Overwrite:=True)
        ts.Write (contents)
        ts.Close
    End If
Next FIL
End Sub

[bulina2k]

just another method (same result)

Option Explicit
Sub file()
    
    Dim sBuf As String
    Dim sTemp As String, i As Long
    Dim iFileNum As Integer
    Dim sFileName As String
    Dim textFileName As String
    
    sFileName = "C:\Users\Bogdan.....\" '' <- path to the folder with txt files followed by "\"
    textFileName = Dir$(sFileName & "*.txt")
    Do While textFileName <> ""
        iFileNum = FreeFile()
        Open sFileName & textFileName For Input As iFileNum
        Do Until EOF(iFileNum)
            Line Input #iFileNum, sBuf
            sTemp = sTemp & sBuf & vbCrLf
        Loop
        Close iFileNum
        For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row
            sTemp = Replace(sTemp, Range("A" & i), Range("B" & i))
        Next
        iFileNum = FreeFile
        Open sFileName & textFileName For Output As iFileNum
        Print #iFileNum, sTemp
        Close iFileNum
        sTemp = ""
        textFileName = Dir$()
    Loop
    MsgBox "Done!"
End Sub

[ian762]

I did not mention, that the text files I am wanting to search through aren't actually ".txt" files. They are ".gdfx" files, they open in plain text, if you do right-click edit in notepad. I see in the examples you guys left, they are looking for ".txt" files. I tried to change that part of the code to be ".gdfx", but I still am not able to get it to work.

[bulina2k]

I added a code to rename all the files in the directory to txt and open them and then to rename them back. try this way:

Option Explicit
Sub file()
    
    Dim sBuf As String
    Dim sTemp As String, i As Long
    Dim iFileNum As Integer
    Dim sFileName As String
    Dim textFileName As String
    
    sFileName = "C:\Users\Bogdan\.......\" '' <- path to the folder with gdfx files followed by "\"
    
    renameallfiles sFileName
    
    textFileName = Dir$(sFileName & "*.txt")
    Do While textFileName <> ""
        iFileNum = FreeFile()
        Open sFileName & textFileName For Input As iFileNum
        Do Until EOF(iFileNum)
            Line Input #iFileNum, sBuf
            sTemp = sTemp & sBuf & vbCrLf
        Loop
        Close iFileNum
        For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row
            sTemp = Replace(sTemp, Range("A" & i), Range("B" & i))
        Next
        iFileNum = FreeFile
        Open sFileName & textFileName For Output As iFileNum
        Print #iFileNum, sTemp
        Close iFileNum
        sTemp = ""
        textFileName = Dir$()
    Loop
    renameallfiles sFileName
    MsgBox "Done!"
End Sub


Sub renameallfiles(xDir As String)

Dim xFile As String

    xFile = Dir(xDir & "*")
    Do Until xFile = ""
        If InStr(xFile, ".txt") > 0 Then
            Name xDir & xFile As xDir & Replace(xFile, ".txt", "")
        Else
            Name xDir & xFile As xDir & xFile & ".txt"
       End If
        xFile = Dir
    Loop
End Sub

[ian762]

I got it figured out. I wasn't searching for the whole string. I changed the search text to be the entire string and it is working now. Thanks for your help guys. This is saving me hours of work!

[bulina2k]

What search text?
Haven't you used any of our code?

[ian762]

the text i needed changed in the file was xxx.xxx.xx, but it was actually part of a larger string yyyy="xxx.xxx.xx", so when i was only looking for xxx.xxx.xx, nothing was working. when I searched for the whole string it worked great.

[GeneralDisarray]

glad you got it working... did my code help? :D

[ian762]

yes, GeneralDissarray, I was using your code. I had it working yesterday before I left work. Now, of course I can't seem to run it. I am not sure what the issue is right now. I attached some example files, maybe you could try and see what I am missing.

[ian762]

I uploaded incorrect files, please use this folder, if you have time to check it out. Thanks

[ian762]

I think I figured out my issue. Maybe you can tell me if I am right.

I finally got it to work when I copied and pasted into columns A and B using "paste special" and choosing text only values not format. Then I was able to run the code with the expected values. I assume when I did a direct copy and paste that I was overwriting some sort of formatting you had set up in column A and B to set range for the VB loop?

At any rate I have it working now. Thanks Again!