I have a large number of Excel Templates that contain VBA code that need to be updated. The Find method of the code-module object only returns true/false, not the location of the found string.
Is there any way to automate the find-and-replace procedure?
Add this code to a new macro-enabled workbook. Set the FIND_WHAT
and REPLACE_WITH
constants, open the other workbooks and run the code.
The original code comes from Charles Pearson's site
WARNING: Only basic testing has been done!
Option Explicit
Sub ReplaceTextInCodeModules()
' Must add a reference to "Microsoft Visual Basic For Applications Extensibility 5.3"
' Also must set "Trust access to the VBA project object model"
' See the url below for more info on these.
' Based on code found at:
' Source: www.cpearson.com/excel/vbe.aspx Copyright 2013, Charles H. Pearson
Dim theWorkbook As Workbook
Dim VBProj As VBIDE.VBProject
Dim VBComp As VBIDE.VBComponent
Dim CodeMod As VBIDE.CodeModule
Dim numLines As Long ' end line
Dim lineNum As Long
Dim thisLine As String
Dim message As String
Dim numFound As Long
Const FIND_WHAT As String = "findthis"
Const REPLACE_WITH As String = "replaced"
numFound = 0
For Each theWorkbook In Application.Workbooks
If theWorkbook.Name <> ThisWorkbook.Name Then
If theWorkbook.HasVBProject Then
Set VBProj = theWorkbook.VBProject
For Each VBComp In VBProj.VBComponents
'Set VBComp = VBProj.VBComponents("Module1")
Set CodeMod = VBComp.CodeModule
With CodeMod
numLines = .CountOfLines
For lineNum = 1 To numLines
thisLine = .Lines(lineNum, 1)
If InStr(1, thisLine, FIND_WHAT, vbTextCompare) > 0 Then
message = message & theWorkbook.Name & " | " & VBComp.Name & " | Line #" & lineNum & vbNewLine
.ReplaceLine lineNum, Replace(thisLine, FIND_WHAT, REPLACE_WITH, , , vbTextCompare)
numFound = numFound + 1
End If
Next lineNum
End With
Next VBComp
End If
End If
Next theWorkbook
Debug.Print "Found: " & numFound
If message <> "" Then
Debug.Print message
End If
End Sub