Mike,
Try the macro below on a subset of your files first, copied into the
folder C:\Test Folder. It will require that you set a reference to MS
VBA Extensibility.
Then after testing and to run it, change
.LookIn = "C:\Test Folder"
.SearchSubFolders = False
to
.LookIn = "C:\" 'or other drive
.SearchSubFolders = True 'to do all folders
HTH,
Bernie
MS Excel MVP
Sub FixAllFilesOnDrive()
Application.DisplayAlerts = False
With Application.FileSearch
.NewSearch
.LookIn = "C:\Test Folder"
.SearchSubFolders = False
.FileType = msoFileTypeExcelWorkbooks
If .Execute 0 Then
For i = 1 To .FoundFiles.Count
Workbooks.Open .FoundFiles(i)
ReplaceCodeInModule ActiveWorkbook
ActiveWorkbook.Save
ActiveWorkbook.Close
Next i
End If
End With
Application.DisplayAlerts = True
End Sub
Sub ReplaceCodeInModule(inBook As Workbook)
Dim myCode As String
Dim myComp As VBComponent
For Each myComp In inBook.VBProject.VBComponents
With myComp.CodeModule
myCode = .Lines(1, .CountOfLines)
myCode = Replace(myCode, _
"\\server1\directory\subdir\file.xls", _
"\\server2\directory\subdir\file.xls")
..DeleteLines 1, .CountOfLines
..InsertLines .CountOfLines + 1, myCode
End With
Next myComp
End Sub
"Mike Enfield" wrote in message
...
We're migrating a large amount of files from one storage
system to another due to capacity constraints. We have a
set of excel files that contain VB macros with hard coded
links to the full URL path of some other files that are
also being moved. Does anyone know if there is a way to
do a global search and replace of text within Excel VB
macros for multiple Excel Spreadsheets?
I.E., original macro may contain text:
\\server1\directory\subdir\file.xls
We want to change any instances of that reference to:
\\server2\directory\subdir\file.xls
Thanks!
Mike