Solved: .TXT Batch replacing Macros

[afzalw]

I am looking for a Macro that replace my text files. It should be able to search a same work from all txt files and replace it with a different word for each file. For example I have same word "TEXT" in all my 1000 files, I want to replace this word in each file by a different word like "TEXT1" for first file, "TEXT2" for 2nd file, TEXT3" for 3rd file...."TEXT1000" for file.

I don't want to to go in each file and find this word and replace it.I have tried software like ReplaceText2.2, Power GREP, Advance Replace tools batch editing software but nothing worked for me. If anyone can help me building this Macro if its possible in excel.

I'll try to further simplify the statement.
I have 1000 files.
File 1 Search "11111" Replace it with "0.324"
File 2 Search "11111" Replace it with "0.932"
File 3 Search "11111" Replace it with "0.842"
File 4 Search "11111" Replace it with "0.642"
File 5 Search "11111" Replace it with "0.652"
.
.
.
File 1000 Search "11111" Replace it with "0.442"
Now you see all the search terms are "11111" but replace terms are different for each file.

[CatDaddy]

what do the full file names look like?

[snb]

You can run this code in Excel, Access, Outlook, Word or Powerpoint...


[VBA]
sub snb()
c00="G:\OF\"
c01=dir(c00 & "*.txt")

with createobject("scripting.filesystemobject")
do until c01=""
.createtextfile(c00 & c01).write replace(.opentextfile(c00 & c01).readall,"11111","0.324")
c01=dir
loop
end with
end sub
[/VBA]

[Kenneth Hobs]

Text1, Text2, and such are easily done. For the number replacement, are you doing that by some logic or a range of cell values or?

[afzalw]

I would have three columns in my excel file
First columns will have paths of my files text files
Second will be the search term
Third will be Replace term
And there would be 1000 rows, each row will have thee columns path, search term and replace terms
Means each row represent data for 1 file

I can get the path of all text files from this script from snb

[vba]Sub ExcelFilespathfromfolderandsubfolderssnb()
c00 = "h:\Study\*.txt"
sn = Application.Transpose(Split(CreateObject("wscript.shell").exec("cmd /c Dir " & c00 & " /b /s").stdout.readall, vbCrLf))
Cells(1).Resize(UBound(sn)) = sn
End Sub
[/vba]
Now how can I make it to run 1000 times search and replace for each file.

[afzalw]

Thanks your code replaced `11111` in all 1000 file with `0.324`. I thought it wasn't possible in VBA. How can I make it to read the path, search text ànd replace term from the excel columns.

I want to replace each file with a different term.
Text files paths are in first column
Search terms in 2nd column
replace terms in 3rd column

[afzalw]

snb can you look at this code and see what`s wrong with it I edit your code to read path, search and replace terms from my excel file. Thanks

[vba]Sub snbFullyEdit()
Dim Path
Dim Search
Dim Replace
With ThisWorkbook.Sheets(1) 'or ThisWorkbook.Sheets(1)
Path = .Range("A1", .Cells(Rows.Count, 1).End(xlUp)).Value
Search = .Range("B1", .Cells(Rows.Count, 1).End(xlUp)).Value
Replace = .Range("C1", .Cells(Rows.Count, 1).End(xlUp)).Value
End With

With CreateObject("scripting.filesystemobject")
For i = LBound(Path) To UBound(Path)
.createtextfile(Path(i, 1)).write Replace(.opentextfile(Path(i, 1)).readall, "Search(i,1)", "Replace(i,1)")
Next i
End With
End Sub
[/vba]
It gives error in the following line
.createtextfile(Path(i, 1)).write Replace(.opentextfile(Path(i, 1)).readall, "Search(i,1)", "Replace(i,1)")

My paths in excel are is in this form (e:\a\1017 1998 - 2009 (1).txt) I think it needs another kind of path, it could be something dealing with the Dir() you used but I did not used that here. I don't know what Dir() do in your code. Your version of the code worked for me, but I cant seem to find the mistake in this one.

[afzalw]

I think that line should be like this, but still it gives Subscript out of range (Error 9)
.createtextfile(Path(i, 1)).write Replace(.opentextfile(Path(i, 1)).readall, (Search(i, 1)), (Replace(i, 1)))

[mancubus]

so you have file names with full path in col A, serach string in col B and replacement in col C...

if this is the case, the code provided may be adopted...
[vba]
Sub snb()

Dim i As Long, LastRow As Long

LastRow = Sheets(1).Cells(Rows.Count, 1).End(xlUp).Row

With CreateObject("scripting.filesystemobject")
For i = 2 To LastRow 'assuming row 1 houses col heads
.createtextfile(Cells(i, 1)).write Replace(.opentextfile(Cells(i, 1)).readall, Cells(i, 2), Cells(i, 3))
Next
End With

End Sub
[/vba]

[snb]

please post your workbook....

[afzalw]

Thankyou mancubus it exactly what i wanted. I tried many software's but nothing worked for me but this VBA code worked like I wanted. Thanks snb you are always great help.

[CatDaddy]

[VBA]Sub cycle_txt_files()
Dim srcFldr, fileEx, srchKey, filePath, dstfilePath, temp As String
Dim srcFldrs() As String
Dim spl() As String
Dim cc, i, j, FN As Integer
Dim oFSO As New FileSystemObject
Dim nFSO As New FileSystemObject
Dim nFSOFile, oFSOFile As TextStream
Dim str As Variant
Dim replacements() As Variant
srcFldr = "C:\TEST"
FileType = "*.txt"
srchKey = "11111"
replacements = Array("0.324", "0.932", "0.842") 'Add remaining 997 values
i = 0
If Dir("C:\RESULTS", vbDirectory) = "" Then MkDir "C:\RESULTS"
filePath = Dir$(srcFldr & "\" & fileEx)
Do Until filePath = ""
Set oFSOFile = oFSO.OpenTextFile(srcFldr & "\" & filePath)

spl = Split(filePath, "\")
j = 1

For Each str In spl
If InStr(CStr(str), fileEx) Then
temp = CStr(str)
Exit For
End If
Next str

spl = Split(temp, ".")
dstfilePath = "C:\RESULTS\" & spl(0) & (i + 1) & ".txt"
Set nFSOFile = nFSO.CreateTextFile(dstfilePath, True)
Do Until oFSOFile.AtEndOfStream
sText = oFSOFile.ReadLine
sText = Replace(sText, srchKey, CStr(replacements(i)))
nFSOFile.WriteLine (sText)
Loop

i = i + 1
oFSOFile.Close
nFSOFile.Close
filePath = Dir$()
Loop
End Sub[/VBA]

[macropod]

Cross-posted at: http://www.mrexcel.com/forum/general...ml#post3243651
For cross-posting etiquette, please read: http://www.excelguru.ca/content.php?184