Hey! I know that password!

Tim

--
Tim Williams
Palo Alto, CA


"Brett Smith" wrote in message
...
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?

"Brett Smith" wrote:

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.

"Bernie Deitrick" wrote:

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" wrote in message
...
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