Changing code Modules, PLEASE HELP!!

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

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" <[email protected]> wrote in message
news:[email protected]...
>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,

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" <[email protected]> wrote in message
> news:[email protected]...
> >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,
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" <[email protected]> wrote in message
> > news:[email protected]...
> > >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
> > >
> >
> >
> >

Hey! I know that password!

Tim

--
Tim Williams
Palo Alto, CA


"Brett Smith" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > >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
> > > >
> > >
> > >
> > >

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

--
Tim Williams
Palo Alto, CA


"Brett Smith" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > >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
> > > >
> > >
> > >
> > >

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
>> > news:[email protected]...
>> > >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,
Thanks alot, I really appreciate it. I was actually testing an array before
you posted. This all makes perfect sense!!!

"Bernie Deitrick" wrote:

> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> >> > news:[email protected]...
> >> > >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
> >> > >
> >> >
> >> >
> >> >
>
>
>

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" wrote:

> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> >> > news:[email protected]...
> >> > >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
> >> > >
> >> >
> >> >
> >> >
>
>
>