Find/Replace on unopened files

[Joe]

If I have a bunch of files and I know that there's
something I want replaced in each of them, can a replace
be done on the files without opening each file and going
through the find/replace thing?

Example: I have 24 files: Bud2003-01.xls, Bud2003-
02.xls, Bud2003-03.xls...Act2003-01.xls, Act2003-02.xls...

Every one of the files have a sheet with the
name "Sheet1". And I know that in Sheet1 of every
workbook, "Monthly" is mis-spelled. It's "Monthy".

I want a macro to do the replace on every file
automatically. Thanks.

 

[Ron de Bruin]

Hi Joe

Copy the 24 files in a folder named C:\Data
And try this sub that will change cell A3 in each cell to Monthly

Sub test()
Dim mybook As Workbook
Dim FNames As String
Dim MyPath As String
Dim SaveDriveDir As String

SaveDriveDir = CurDir
MyPath = "C:\Data"
ChDrive MyPath
ChDir MyPath
FNames = Dir("*.xls")
If Len(FNames) = 0 Then
MsgBox "No files in the Directory"
ChDrive SaveDriveDir
ChDir SaveDriveDir
Exit Sub
End If
Application.ScreenUpdating = False
Do While FNames <> ""
Set mybook = Workbooks.Open(FNames)
mybook.Worksheets("Sheet1").Range("A3").Value = "Monthly"
mybook.Close True
FNames = Dir()
Loop
ChDrive SaveDriveDir
ChDir SaveDriveDir
Application.ScreenUpdating = True
End Sub