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
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)
What's the fullpath you are looking in ?
vblearner44
06-13-2013, 01:37 AM
The full path is 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!!!!
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.