Serach and Replace across multiple files

[verizon]

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

 

[DomThePom]

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

 

[DomThePom]

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
******************************************

For help with Microsoft Scripting runtime search for it on
http://msdn.microsoft.com/