amitcohen
Board Regular
- Joined
- Jan 14, 2010
- Messages
- 118
Hi All
I'm trying to build a macro the replace string (text) with another string.
"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
I'm trying to build a macro the replace string (text) with another string.
Code:
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
"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