ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Changing code Modules, PLEASE HELP!! (https://www.excelbanter.com/excel-programming/351632-changing-code-modules-please-help.html)

Brett Smith[_2_]
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

Bernie Deitrick
Changing code Modules, PLEASE HELP!!
 
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

Brett Smith[_2_]
Changing code Modules, PLEASE HELP!!
 
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

Brett Smith[_2_]
Changing code Modules, PLEASE HELP!!
 
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

Tim Williams
Changing code Modules, PLEASE HELP!!
 
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

Tim Williams
Changing code Modules, PLEASE HELP!!
 
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" 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

Bernie Deitrick
Changing code Modules, PLEASE HELP!!
 
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","F String_5","FString_6")
myrStr = Array( "RString_1","RString_2","RString_3","RString_4","R String_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" 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

Brett Smith[_2_]
Changing code Modules, PLEASE HELP!!
 
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","F String_5","FString_6")
myrStr = Array( "RString_1","RString_2","RString_3","RString_4","R String_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" 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

Brett Smith[_2_]
Changing code Modules, PLEASE HELP!!
 
Thank you all for your help!! I really appreciate your answers!!

"Brett Smith" wrote:

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","F String_5","FString_6")
myrStr = Array( "RString_1","RString_2","RString_3","RString_4","R String_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" 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

Brett Smith[_2_]
Changing code Modules, PLEASE HELP!!
 
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","F String_5","FString_6")
myrStr = Array( "RString_1","RString_2","RString_3","RString_4","R String_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" 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

Bernie Deitrick
Changing code Modules, PLEASE HELP!!
 
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


"Brett Smith" wrote in message
...
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","F String_5","FString_6")
myrStr = Array( "RString_1","RString_2","RString_3","RString_4","R String_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" 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


All times are GMT +1. The time now is 11:11 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com