Serach and Replace across multiple files
 

Hello Newsgroup

My macro creates *.xls reports as it grinds through its iterative
calculations.

Unfortunately, now that it has finished, each of the 120 output files has an
incorrect text entry in cell D2. Instead of saying "Hello" in D2 I want to
replace it with "Goodbye."

How can I replace "Hello" with "Goodbye" in each of the 120 files without
opening them individually. Is there an easy way to search and replace for
all files within a directory?

Thank you

W

Serach and Replace across multiple files
 

You need to make use of the Microsoft Scripting runtime.

First create a reference to it in the Excel vbs ide (Tools references
and select Microsoft scripting runtime).

Then create a procedure such as:

Sub GetFileNames(strFolder)

Dim fso As New FileSystemObject
Dim dir As Folder
Dim fil As File
Dim wb As Workbook

Set dir = fso.GetFolder(strFolder)
For Each fil In dir.Files
Set wb = Workbooks.Open(fil.Path)
With wb
.Sheets(1).Range("D2").Value = "Goodbye"
.Close (True)
End With

Next fil
Set fso = Nothing
Set dir = Nothing
Set fil = Nothing

End Sub



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/