replacement for filesearch in excel 2007

[Guest]

Hello everyone,

I am trying to do the following.

1 Go to a folder on my computer
2 open the first workbook in the folder
3 change the value of two cells on one tab
4 save the workbook
5 close the workbook
6 repeat until every workbook in the folder has been changed

I will be changing the same two cells with the same two new values on the
same tab name for all workbooks. I tried the following but have since found
out the .filesearch no longer works. Any help would be appreciated.

LWhite

Sub MassEdit()
'
' MassEdit Macro
'
Dim myBook As Workbook, _
ThisSheet As Worksheet, _
sDir, sFile As String

sDir = "C:\MTD\"
sFile = "*.xls"

Set ThisSheet = ActiveWorkbook.Sheets(1)
With Application
.DisplayAlerts = False
With .FileSearch
.NewSearch
.LookIn = sDir
.Filename = sFile
.MatchTextExactly = True
If .Execute < 0 Then Exit Sub
For i = 1 To .FoundFiles.Count - 1
Set myBook = Workbooks.Open(.FoundFiles(i))
With myBook
'
Sheets("Revision").Select
Range("A1").Select
ActiveCell.FormulaR1C1 = "VER"
Range("A2").Select
ActiveCell.FormulaR1C1 = "1"
Range("A3").Select
ActiveWorkbook.Save
ActiveWindow.Close
'
End With
Next i
End With
.DisplayAlerts = True
End With

Set myBook = Nothing
Set ThisSheet = Nothing
'
End Sub

 

[Bernie Deitrick]

Dir still works:

Sub MassEdit2()
'
' MassEdit Macro
'
Dim myBook As Workbook
Dim sDir As String
Dim sFile As String
Dim WorkFile As String

sDir = "C:\Excel\"
sFile = "*.xls"

Application.DisplayAlerts = False
WorkFile = Dir(sDir & sFile)

Do While WorkFile <> ""
Set myBook = Workbooks.Open(Filename:=sDir & WorkFile)
With myBook
myBook.Sheets("Revision").Range("A1").Value = "VER"
myBook.Sheets("Revision").Range("A2").Value = "1"
.Save
.Close False
End With
WorkFile = Dir()
Loop

Application.DisplayAlerts = True
Set myBook = Nothing

End Sub



HTH,
Bernie
MS Excel MVP