Set fs = Application.FileSearch
With fs
.LookIn = "C:\Your\Path\Here"
.Filename = "*.xls"
If .Execute > 0 Then
For i = 1 To .FoundFiles.Count
Workbooks.Open .FoundFiles(i)
'Your Code Here
Next i
End If
End With
Set fs = NothingPublic Sub BatchUpdateWorkbookFormulaPaths()
Dim wkbToUpdate As Workbook
Dim wksToUpdate As Worksheet
Dim rngToUpdate As Range
Dim objFileSearch As FileSearch
Dim intCurrentFile As Integer
Set objFileSearch = Application.FileSearch
'Find the files in Directory 'YourDirectoryHere' in the
'format 'C:\Directory'
With objFileSearch
.LookIn = "[i]YourDirectoryHere[/i]"
.Filename = "*.xls"
.SearchSubFolders = True
If .Execute(SortBy:=msoSortByFileName, _
SortOrder:=msoSortOrderAscending) = 0 Then
MsgBox "There were no files found."
Exit Sub
End If
End With
For intCurrentFile = 1 To objFileSearch.FoundFiles.Count
Set wkbToUpdate = Workbooks.Open(objFileSearch.FoundFiles(intCurrentFile))
For Each wksToUpdate In wkbToUpdate.Worksheets
'Need a range for the Replace method, grab A-Z
Set rngToUpdate = wksToUpdate.Columns("A:Z")
'Make the replacement, update both strings
rngToUpdate.Replace "[i]YourOldPathHere[/i]", "[i]YourNewPathHere[/i]"
Next wksToUpdate
[b]'Might be a good idea to do a .SaveAs and move to the new
'server location in the same step. This way you don't 'taint'
'the original files if the code is wrong[/b]
wkbToUpdate.Save
wkbToUpdate.Close
Next intCurrentFile
CleanUp:
Set rngToUpdate = Nothing
Set wksToUpdate = Nothing
Set wkbToUpdate = Nothing
End Subbecause it amazing how quickly a macro can screw up a couple hundred files before you know it.Me said:
