Find and replace data in a text document

[vblearner44]

Hi,

I am new to VBA and I am trying to write a excel (2003) macro to do find the data in column A of DATA.xls and replace the data with Column B values of DATA.xls in all the opened text documents (around 100).

Can some one please help? Currently am opening the text documents in notepad++ and using the replace in all open documents option to change the text.

Thanks

[patel]

it's not clear for me, need sample files with also desired result

[snb]

[VBA]
Sub M_snb()
sn=thisworkbook.sheets(1).cells(1).currentregion.resize(,2)

With createobject("scripting.filesystemobject")
for each fl in split(createobject("wscript.shell").exec("cmd /c dir G:\OF\*.txt /b").stdout.readall,vbcrlf)

c00=.opentextfile(it).readall
for j=1 to ubound(sn)
c00=replace(c00,sn(j,1),sn(j,2))
next
.createtextfile(it).write c00
next
End With
End with
[/VBA]

NB. You will have to change the path G:\OF\ into the path on your system.

[vblearner44]

Hi Patel,
Thanks for your interest in helping me. Here is the example.

Contents of Data.xls

Column A Column B
Add this to favourites Añadir a favoritos
What would you like to do? ¿Qué desea hacer?

I have several HTML files (say 1.HTML, 2.HTML, 3.HTML, etc) in folder C:\Data.

Open all the HTML files in folder C:\Data and find the data in Column A1 (Add this to favourites) and replace it with data in column B1 (Añadir a favoritos).

Hope this makes sense and let me know if you have more questions.

[patel]

snb solution is very interesting, but need some changes, you can try this
[vba]Sub M_snb()
fpath = "E:\test\" ' <<< to be changed
sn = ThisWorkbook.Sheets(1).Cells(1).CurrentRegion.Resize(, 2)
With CreateObject("scripting.filesystemobject")
For Each fl In Split(CreateObject("wscript.shell").exec("cmd /c dir " & fpath & "*.html /b").stdout.readall, vbCrLf)
c00 = .opentextfile(fpath & fl).readall
For j = 1 To UBound(sn)
c00 = Replace(c00, sn(j, 1), sn(j, 2))
Next
.createtextfile(fpath & fl).write c00
Next
End With
End Sub[/vba]

[vblearner44]

Thanks guys.
I tried running this code with some break points. The command window flashes after the following line, but nothing seems to happen after this as the program ends.

For Each fl In Split(CreateObject("wscript.shell").exec("cmd /c dir " & fpath & "*.html /b").stdout.readall, vbCrLf)

[snb]

What's the fullpath you are looking in ?

[vblearner44]

The full path is C:\data

[snb]

Make it "C:\data\"

[vblearner44]

I tried that yesterday, but was receiving an error 'Path not found', but didnt realise that the data is actually replaced.
Thanks for your help!!!!