search and replace within macros across workbooks?

  • Thread starter Thread starter Mike Enfield
  • Start date Start date
M

Mike Enfield

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
 
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
 
Back
Top