Changing code Modules, PLEASE HELP!!

[Brett Smith]

I am looking to do a search and replace for some code in all the codeModules
in my VBA Macro program. I am trying to do an automated program that
searches for and finds the Excel Workbooks, finds what modules are each
Workbook, and then replaces code. So it would replace XLFit3_ with XLFit4,
but I am not exactly sure how to modify code in modules using the search and
replace function. Anybody's help is more than welcome and thankfully
appreciated. Here is what I have so far. What it does so far is open up the
files that are already there, and loops until there are no more files, but I
don't know how to change the code on the back end of these files. See below
for code. Please don't point me to http://www.cpearson.com's website, I've
been there already and is very informative, but I couldn't find anything
exactly like this. Thanks for your help ahead of time.

Sub FileSearchforMacros()

Dim wkbkOne As Workbook


With Application.FileSearch
.LookIn = "\\RTTMSGFP141\smithb25$\Desktop\testvba"
.SearchSubFolders = True
.FileType = msoFileTypeExcelWorkbooks
If .Execute() > 0 Then
MsgBox "There were " & .FoundFiles.Count & _
" file(s) found."
For i = 1 To .FoundFiles.Count
MsgBox .FoundFiles(i)
Set wkbOne = Application.Workbooks.Open(.FoundFiles(i), , , ,
Password:=("INGRAM"))
wkbOne.Save
wkbOne.Close
Next i
Else
MsgBox "There were no files found."
End If
End With
End Sub

 

[Bernie Deitrick]

Brett,

Try the macro FileSearchforMacros2 below, which calls the macro ReplaceCodeInModule for each file.

HTH,
Bernie
MS Excel MVP

Sub FileSearchforMacros2()
Dim i As Integer
Dim wkbkOne As Workbook

With Application.FileSearch
.LookIn = "\\RTTMSGFP141\smithb25$\Desktop\testvba"
.SearchSubFolders = True
.FileType = msoFileTypeExcelWorkbooks
If .Execute() > 0 Then
MsgBox "There were " & .FoundFiles.Count & _
" file(s) found."
For i = 1 To .FoundFiles.Count
MsgBox .FoundFiles(i)
Set wkbkOne = Application.Workbooks.Open( _
.FoundFiles(i), , , , Password:=("INGRAM"))
ReplaceCodeInModule wkbkOne
wkbkOne.Save
wkbkOne.Close
Next i
Else
MsgBox "There were no files found."
End If
End With
End Sub

Sub ReplaceCodeInModule(RepWK As Workbook)
Dim myCode As String
Dim myFStr As String
Dim myRStr As String
Dim myMod As VBComponent

myFStr = "FRTP_1"
myRStr = "FRTP_2"

For Each myMod In RepWK.VBProject.VBComponents
With myMod.CodeModule

If .CountOfLines > 0 Then
myCode = .Lines(1, .CountOfLines)
If InStr(1, myCode, myFStr) > 0 Then
MsgBox myCode
myCode = Replace(myCode, myFStr, myRStr)
MsgBox myCode

..DeleteLines 1, .CountOfLines
..InsertLines .CountOfLines + 1, myCode
End If
End If
End With
Next myMod
End Sub

 

[Brett Smith]

Bernie,

Thank you so very much for the help you have given me. This will get me off
to a good start I'm sure. You have been a HUGE help. I may have more
questions later if you don't mind.

 

[Brett Smith]

Bernie,
What if I have 6 text strings that I want to change as opposed to just 1?
How would I approach that in my code. Write Subs as opposed to the one?

 

[Tim Williams]

Hey! I know that password!

Tim

 

[Tim Williams]

Just modify the Replace.. sub to accept two more parameters and then call
it repeatedly from your main sub.


Sub ReplaceCodeInModule(RepWK As Workbook, StringCurrent as string,
StringNew as string)
Dim myCode As String
Dim myFStr As String
Dim myRStr As String
Dim myMod As VBComponent

'myFStr = "FRTP_1" 'use StringCurrent
' myRStr = "FRTP_2" 'use StringNew

'... etc


Call as:

ReplaceCodeInModule wkbkOne, "findthis", "replacewith"
ReplaceCodeInModule wkbkOne, "findthis2", "replacewith2"
ReplaceCodeInModule wkbkOne, "findthis3", "replacewith3"

If you need more help then you can contact me directly.

Tim

 

[Bernie Deitrick]

Brett,

Try replacing ReplaceCodeInModule with the sub below.

HTH,
Bernie
MS Excel MVP

Sub ReplaceCodeInModule(RepWK As Workbook)
Dim myCode As String
Dim myFStr As Variant
Dim myRStr As Variant
Dim myMod As VBComponent
Dim i As Integer

myFStr = Array( "FString_1","FString_2","FString_3","FString_4","FString_5","FString_6")
myrStr = Array( "RString_1","RString_2","RString_3","RString_4","RString_5","RString_6")

For Each myMod In RepWK.VBProject.VBComponents
With myMod.CodeModule

If .CountOfLines > 0 Then
myCode = .Lines(1, .CountOfLines)
For i = LBound(myFStr) to UBound(myFStr) Then
myCode = Replace(myCode, myFStr(i), myRStr(i))
Next i
..DeleteLines 1, .CountOfLines
..InsertLines .CountOfLines + 1, myCode
End If
End If
End With
Next myMod
End Sub

 

[Brett Smith]

Bernie,
Thanks alot, I really appreciate it. I was actually testing an array before
you posted. This all makes perfect sense!!!

 

[Brett Smith]

Thank you all for your help!! I really appreciate your answers!!

 

[Brett Smith]

Hi Bernie,
It seems to work except FString4_, Fstring5_ and FString_6 I don't think was
changed when I ran the program. is there any reason for this? How would I
fix this problem? Thanks!

 

[Bernie Deitrick]

This worked fine for me (I had an extra "Then" and "End If " in the first version)

Sub ReplaceCodeInModule(RepWK As Workbook)
Dim myCode As String
Dim myFStr As Variant
Dim myRStr As Variant
Dim myMod As VBComponent
Dim i As Integer

myFStr = Array("FString_1", "FString_2", "FString_3", "FString_4", "FString_5", "FString_6")
myRStr = Array("RString_1", "RString_2", "RString_3", "RString_4", "RString_5", "RString_6")

For Each myMod In RepWK.VBProject.VBComponents
With myMod.CodeModule

If .CountOfLines > 0 Then
myCode = .Lines(1, .CountOfLines)
For i = LBound(myFStr) To UBound(myFStr)
myCode = Replace(myCode, myFStr(i), myRStr(i))
Next i
..DeleteLines 1, .CountOfLines
..InsertLines .CountOfLines + 1, myCode
End If
End With
Next myMod
End Sub

HTH,
Bernie
MS Excel MVP