REPLACE across multiple excel files

J

Jon

I've built a series of data sheets, across several different folders o
our network drive.

I now need to replace one of the names in the data with another one.
e.g. "Jimmy" > "James"

Is there any cunning macro that would recursively step through eac
file in each folder, replacing the text: before I resort to doing i
manually.

:(

Cheers,
Jo
 
J

jeff

Hi,

I don't know how cunning this macro is, but it
looks for "john" and replaces with "BIGJOHN".

Try on TEST files 1st - you have to itemize each filename
in this macro - there are other ways to get to the
files, but I assumed your files were spread out
across various directories and had very different
names, so no "pattern" would be easy.
(Here I'm getting files "C:\temptemp\Sub1.xls"
and "..2.xls"

jeff

Sub ReplaceAll()
Dim WkBkCount As Integer
Dim wk As Workbook
Dim sh As Sheets
Dim AllWkbks(5) As String
WkBkCount = 2 ' number of files to substitute
'list of files (or use DIR statement if all in
' same directory)
AllWkbks(1) = "C:\temptemp\Sub1.xls"
AllWkbks(2) = "C:\temptemp\Sub2.xls"
For k = 1 To WkBkCount
Workbooks.Open Filename:=AllWkbks(k)
Set wk = ActiveWorkbook
Set sh = wk.Sheets
For j = 1 To sh.Count
sh(j).Select
Cells.Replace What:="john",
Replacement:="BIGJOHN", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=True,
SearchFormat:=False, _
ReplaceFormat:=False
Next j
ActiveWorkbook.Close 'savechanges:=True
Next k
End Sub
 
Top