Replacing code

M

melanie_jacks

I have about 200 files where I need to change one word in the code
from "prop" to "east".
Instead of opening each file and doing it manually, it would be great
if I could write a macro to rewrite the code for me.

Here's what I have so far which Finds the word in the code. But I do
not know how to go about replacing it.

Dim VBProj As VBIDE.VBProject
Dim VBComp As VBIDE.VBComponent
Dim CodeMod As VBIDE.CodeModule
Dim FindWhat As String
Dim ReplaceWhat As String
Dim SL As Long ' start line
Dim EL As Long ' end line
Dim SC As Long ' start column
Dim EC As Long ' end column
Dim Found As Boolean

Set VBProj = ActiveWorkbook.VBProject
Set VBComp = VBProj.VBComponents("Thisworkbook")
Set CodeMod = VBComp.CodeModule

FindWhat = "prop"
ReplaceWhat = "east"

With CodeMod
SL = 1
EL = .CountOfLines
SC = 1
EC = 255
Found = .Replace(target:=FindWhat, replacement:="east",
StartLine:=SL, StartColumn:=SC, _
EndLine:=EL, EndColumn:=EC, _
wholeword:=True, MatchCase:=False,
patternsearch:=False)

End With


Any help is appreciated.
 
B

Bob Phillips

Dim VBProj As Object
Dim VBComp As Object
Dim CodeMod As Object
Dim FindWhat As String
Dim ReplaceWhat As String
Dim OldLine As String
Dim NewLine As String
Dim SL As Long ' start line
Dim EL As Long ' end line
Dim SC As Long ' start column
Dim EC As Long ' end column
Dim Found As Boolean

Workbooks.Open ("C:\test\Test Test Test.xls")
Set VBProj = ActiveWorkbook.VBProject
Set VBComp = VBProj.VBComponents("Thisworkbook")
Set CodeMod = VBComp.codemodule

FindWhat = "prop"
ReplaceWhat = "east"

SL = 1
With CodeMod

Do
Found = .Find(FindWhat, SL, SC, EL, EC, True, False, False)
If Found Then

OldLine = .Lines(SL, 1)
NewLine = Left$(OldLine, SC - 1) & ReplaceWhat
If EC < Len(OldLine) Then NewLine = NewLine &
Right$(OldLine, Len(OldLine) - EC + 1)
.ReplaceLine SL, NewLine
SL = SL + 1
End If
Loop Until Not Found
End With
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top