Replace text in a txt files

[amitcohen]

Hi All
I'm trying to build a macro the replace string (text) with another string.

Sub Replace_Text_test()
'1. Build list of files in the folder
Range("B:B").Select
Selection.ClearContents
On Error Resume Next
Extension = "*.txt"
Folder = Range("A1").Value
First = True
RowCount = 1
Do
If First = True Then
fName = Dir(Folder & "\" & Extension)
First = False
Else
fName = Dir()
End If
If fName <> "" Then
Range("B" & RowCount) = fName
RowCount = RowCount + 1
End If
Loop While fName <> ""
Range("A1").Select

2. Replace text
Dim i As Long
Dim lps As Long
Dim ReplaceTextInFile As String

lps = Range("C1").Value
  For i = lps To 1 Step -1
    With ActiveSheet
    ReplaceTextInFile = Replace(Range("A1").Value & "\" & Range("B" & i).Value, Range("D1").Value, Range("E1").Value)
'''    'ReplaceTextInFile "C:\Users\WEB\Desktop\test\Text.txt", "zzz", "xxx"
    End With
  Next i
End Sub

"A1" is the container folder: C:\Users\WEB\Desktop\test
"B:B" is the list of all txt files in the given folder
"C1" is the formula =counta() to count how many txt files
"D1" is the text to find
"E1" is the text to replace with.


The problem is that the macro runs with no errors
but its not executing the replace text.


Does any one can spot where the code error is?

Many thanks,
Amiy

 

[wigi]

The problem is that the macro runs with no errors
but its not executing the replace text.

That's true. In your code, you fill a variable (ReplaceTextInFile) but don't seem to do anything with it.

More importantly, what is the code of the procedure: ReplaceTextInFile (you use it as a variable but also as a called procedure)?

Also, why do you loop over the files to store them in the sheet, then loop through the cells in the sheet? You can just loop through the files without the overhead of storing them on the sheet and looping again.

 

[Gavin Collins]

If I understand correctly, you want to create a list of file path/file names in an Excel spreadsheet, and then replace the old names with new file path/file name.

The method I would use for the replacement of the text is:

Cells.Replace _
What:=Range("D1").Value
Replacement:=Range("E1").Value
LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False

 

[amitcohen]

Hi wigi
Thanks for the replay.

Well, funny enough I'm using this code on other macro I have, and it works just fine.

Here is the original code:

'Build list of files in the folder
Range("B:B").Select
Selection.ClearContents
On Error Resume Next
Extension = "*.txt"
Folder = Range("A1").Value
First = True
RowCount = 1
Do
If First = True Then
fName = Dir(Folder & "\" & Extension)
First = False
Else
fName = Dir()
End If
If fName <> "" Then
Range("B" & RowCount) = fName
RowCount = RowCount + 1
End If
Loop While fName <> ""
Range("A1").Select

Dim i As Long
Dim lps As Long
lps = Range("C1").Value
For i = lps To 1 Step -1
    With ActiveSheet
    [COLOR=DarkRed]ReplaceTextInFile Range("A1").Value & "\" & Range("B" & i).Value, Range("D1").Value, Range("E1").Value[/COLOR]
    End With
Next i

Then, I took that code and installed it in a new excel workbook.
It didn't worked.

Then I tried to manipulate it, failed and ending posting this on the forum.
i.e the first piece of code.

As you can see, the 'ReplaceTextInFile' variable doesn't realy have a value
But it workes on the original macro, but not om the new one.

As to your other question, I need the list of files in the B:B so I can make a report of whet file has been updated.

Hope I manage to explain myself here (As English is not my first language )

Thanks,
Amit
Thanks

 

[wigi]

ReplaceTextInFile will most probably be a call to another procedure (ReplaceTextInFile). So my guess is that you haven't copied that code in the new file.

 

[amitcohen]

Hi Gavin
Excel is only storage data for the VBA to run on txt files
The replace text is done on a list of txt files (and not on the excel's cells)

 

[Gavin Collins]

When you say it is done "on" txt files, do you mean

• you are using ReplaceTextInFile to change the filename of the text file itself (e.g. xxx.txt becomes zzz.txt); or
• you are using ReplaceTextInFile to change text within the file xxx.txt?

If you mean the latter, then I think you need to use something like this.

 

[wigi]

Consider this code (instead of all that code in the exceltip article Gavin liked you to):

Sub MAIN()
    
    Dim FileContents As String

    Set fldr = CreateObject("scripting.filesystemobject").Getfolder(folder)

    For Each fl In fldr.Files
        If Right(fl.Name, 4) = ".txt" Then

            Open fldr.Path & "\" & fl.Name For Input As #1
            FileContents = Input(LOF(1), #1)
            Close #1

            Open fldr.Path & "\" & fl.Name For Output As #1
            Print #1, Replace(FileContents, "zzz", "xxx")
            Close #1

        End If
    Next

End Sub

folder on line 2 is your variable for cell A1 contents.

 

[amitcohen]

This is great!
Works like a charm.


Thank you for the help

Amit