PDA

View Full Version : Find and replace data in a text document



vblearner44
06-11-2013, 06:01 AM
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
06-11-2013, 06:15 AM
it's not clear for me, need sample files with also desired result

snb
06-11-2013, 06:34 AM
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


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

vblearner44
06-11-2013, 06:35 AM
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
06-11-2013, 12:12 PM
snb solution is very interesting, but need some changes, you can try this
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

vblearner44
06-12-2013, 04:17 AM
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
06-12-2013, 04:23 AM
What's the fullpath you are looking in ?

vblearner44
06-13-2013, 01:37 AM
The full path is C:\data

snb
06-13-2013, 02:05 AM
Make it "C:\data\"

vblearner44
06-17-2013, 04:57 AM
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!!!!