Hello,

I have a set of workbooks that all have a similar macro in them. Each of
these macros contains a string I need to change, but to change it manually
in each file is time prohibitive - 60+ files.

Given I already have one of these files open with a seperate macro, from the
seperate macro, how do I access the module - say Module1, search for the
line that contains the text needing to be changed, change it and then close
the file?

I already have the list of files and the loop to go through all of them. I
am just unclear on how to change the Module1/Macro text from another macro.

Any ideas?

Thank you very much in advance.

Eric Pearce

Hi there Eric,

Rather than search your code for one line, why not replace the entire
module? It would seem to me that would be a little easier. If you Export
the Module in question to a known location on your computer (I used my
desktop), you can use something like this ...

Sub ImportBAS()
Dim strBASpath As String
strBASpath = "C:\Documents and Settings\Rob\Desktop\Module1.bas"
Application.VBE.ActiveVBProject.VBComponents.Impor t strBASpath
End Sub

Obviously, change the 'strBASpath' to the path/name your Module .BAS file is
located.* Note that it will not overwrite any Modules that are currently in
the system by the same name. If you need to delete a Module, you would need
to add some code like this ...

Dim VBmod As Object
Set VBmod = ThisWorkbook.VBProject.VBComponents("Module1")
Application.VBE.ActiveVBProject.VBComponents.Remov e VBmod

*To Export your Module, right click the Modue | Export.., choose location,
Ok. Remember to SAVE YOUR WORK BEFORE YOU RUN ANY OF THIS CODE!
Adding/Deleting Modules can be very serious and you should have backups
already in place anytime you run procedures such as this.

--
Regards,
Zack Barresse, aka firefytr


"Eric" wrote in message
...
Hello,

I have a set of workbooks that all have a similar macro in them. Each of
these macros contains a string I need to change, but to change it manually
in each file is time prohibitive - 60+ files.

Given I already have one of these files open with a seperate macro, from
the
seperate macro, how do I access the module - say Module1, search for the
line that contains the text needing to be changed, change it and then
close
the file?

I already have the list of files and the loop to go through all of them. I
am just unclear on how to change the Module1/Macro text from another
macro.

Any ideas?

Thank you very much in advance.

Eric Pearce

This would work wonderfully, except I failed to mention (my bad) that the
code resides in the Workbook_Open function, in the 'Thisworkbook' module.
That module cannot be removed.

Also, the macro that is supposed to be running all of this is located in a
completely seperate workbook so I need to be able to access the right VB
project. I liked the suggestion of just deleting what is there and replace
it with either code in an external file, or in the macro itself. The
external file would be easier for the users understand.

Any additional help will be and is greatly appreciated.

Eric Pearce

"zackb" wrote in message
...
Hi there Eric,

Rather than search your code for one line, why not replace the entire
module? It would seem to me that would be a little easier. If you Export
the Module in question to a known location on your computer (I used my
desktop), you can use something like this ...

Sub ImportBAS()
Dim strBASpath As String
strBASpath = "C:\Documents and Settings\Rob\Desktop\Module1.bas"
Application.VBE.ActiveVBProject.VBComponents.Impor t strBASpath
End Sub

Obviously, change the 'strBASpath' to the path/name your Module .BAS file
is
located.* Note that it will not overwrite any Modules that are currently
in
the system by the same name. If you need to delete a Module, you would
need
to add some code like this ...

Dim VBmod As Object
Set VBmod = ThisWorkbook.VBProject.VBComponents("Module1")
Application.VBE.ActiveVBProject.VBComponents.Remov e VBmod

*To Export your Module, right click the Modue | Export.., choose location,
Ok. Remember to SAVE YOUR WORK BEFORE YOU RUN ANY OF THIS CODE!
Adding/Deleting Modules can be very serious and you should have backups
already in place anytime you run procedures such as this.

--
Regards,
Zack Barresse, aka firefytr


"Eric" wrote in message
...
Hello,

I have a set of workbooks that all have a similar macro in them. Each
of
these macros contains a string I need to change, but to change it
manually
in each file is time prohibitive - 60+ files.

Given I already have one of these files open with a seperate macro, from
the
seperate macro, how do I access the module - say Module1, search for the
line that contains the text needing to be changed, change it and then
close
the file?

I already have the list of files and the loop to go through all of them.
I
am just unclear on how to change the Module1/Macro text from another
macro.

Any ideas?

Thank you very much in advance.

Eric Pearce

Well, here are 2 examples for you. When prompted press Continue ...

Sub DeleteTheOneLine()
ThisWorkbook.VBProject.VBComponents("ThisWorkbook" ).CodeModule.DeleteLines
5, 1
End Sub
Sub InsertTheNewLine()
Dim strCode As String
strCode = " MsgBox ""Hello!"" & vbCrLf & ""I'm a new line!"""
ThisWorkbook.VBProject.VBComponents("ThisWorkbook" ).CodeModule.InsertLines
5, strCode
End Sub

Note that you MUST know the exact line of code that you want to get rid of.
The first of the two numbers is the line to start deleting/inserting on and
the second number is the number of lines to delete/insert.

--
Regards,
Zack Barresse, aka firefytr


"Eric" wrote in message
...
This would work wonderfully, except I failed to mention (my bad) that the
code resides in the Workbook_Open function, in the 'Thisworkbook' module.
That module cannot be removed.

Also, the macro that is supposed to be running all of this is located in a
completely seperate workbook so I need to be able to access the right VB
project. I liked the suggestion of just deleting what is there and
replace
it with either code in an external file, or in the macro itself. The
external file would be easier for the users understand.

Any additional help will be and is greatly appreciated.

Eric Pearce

"zackb" wrote in message
...
Hi there Eric,

Rather than search your code for one line, why not replace the entire
module? It would seem to me that would be a little easier. If you
Export
the Module in question to a known location on your computer (I used my
desktop), you can use something like this ...

Sub ImportBAS()
Dim strBASpath As String
strBASpath = "C:\Documents and Settings\Rob\Desktop\Module1.bas"
Application.VBE.ActiveVBProject.VBComponents.Impor t strBASpath
End Sub

Obviously, change the 'strBASpath' to the path/name your Module .BAS file
is
located.* Note that it will not overwrite any Modules that are currently
in
the system by the same name. If you need to delete a Module, you would
need
to add some code like this ...

Dim VBmod As Object
Set VBmod = ThisWorkbook.VBProject.VBComponents("Module1")
Application.VBE.ActiveVBProject.VBComponents.Remov e VBmod

*To Export your Module, right click the Modue | Export.., choose
location,
Ok. Remember to SAVE YOUR WORK BEFORE YOU RUN ANY OF THIS CODE!
Adding/Deleting Modules can be very serious and you should have backups
already in place anytime you run procedures such as this.

--
Regards,
Zack Barresse, aka firefytr


"Eric" wrote in message
...
Hello,

I have a set of workbooks that all have a similar macro in them. Each
of
these macros contains a string I need to change, but to change it
manually
in each file is time prohibitive - 60+ files.

Given I already have one of these files open with a seperate macro,
from
the
seperate macro, how do I access the module - say Module1, search for
the
line that contains the text needing to be changed, change it and then
close
the file?

I already have the list of files and the loop to go through all of
them.
I
am just unclear on how to change the Module1/Macro text from another
macro.

Any ideas?

Thank you very much in advance.

Eric Pearce

I had to make some minor changes to go after the code in the other workbook,
but it worked great. Thank you for your help.

Eric Pearce

"zackb" wrote in message
...
Well, here are 2 examples for you. When prompted press Continue ...

Sub DeleteTheOneLine()

ThisWorkbook.VBProject.VBComponents("ThisWorkbook" ).CodeModule.DeleteLines
5, 1
End Sub
Sub InsertTheNewLine()
Dim strCode As String
strCode = " MsgBox ""Hello!"" & vbCrLf & ""I'm a new line!"""

ThisWorkbook.VBProject.VBComponents("ThisWorkbook" ).CodeModule.InsertLines
5, strCode
End Sub

Note that you MUST know the exact line of code that you want to get rid
of.
The first of the two numbers is the line to start deleting/inserting on
and
the second number is the number of lines to delete/insert.

--
Regards,
Zack Barresse, aka firefytr


"Eric" wrote in message
...
This would work wonderfully, except I failed to mention (my bad) that
the
code resides in the Workbook_Open function, in the 'Thisworkbook'
module.
That module cannot be removed.

Also, the macro that is supposed to be running all of this is located in
a
completely seperate workbook so I need to be able to access the right VB
project. I liked the suggestion of just deleting what is there and
replace
it with either code in an external file, or in the macro itself. The
external file would be easier for the users understand.

Any additional help will be and is greatly appreciated.

Eric Pearce

"zackb" wrote in message
...
Hi there Eric,

Rather than search your code for one line, why not replace the entire
module? It would seem to me that would be a little easier. If you
Export
the Module in question to a known location on your computer (I used my
desktop), you can use something like this ...

Sub ImportBAS()
Dim strBASpath As String
strBASpath = "C:\Documents and Settings\Rob\Desktop\Module1.bas"
Application.VBE.ActiveVBProject.VBComponents.Impor t strBASpath
End Sub

Obviously, change the 'strBASpath' to the path/name your Module .BAS
file
is
located.* Note that it will not overwrite any Modules that are
currently
in
the system by the same name. If you need to delete a Module, you would
need
to add some code like this ...

Dim VBmod As Object
Set VBmod = ThisWorkbook.VBProject.VBComponents("Module1")
Application.VBE.ActiveVBProject.VBComponents.Remov e VBmod

*To Export your Module, right click the Modue | Export.., choose
location,
Ok. Remember to SAVE YOUR WORK BEFORE YOU RUN ANY OF THIS CODE!
Adding/Deleting Modules can be very serious and you should have backups
already in place anytime you run procedures such as this.

--
Regards,
Zack Barresse, aka firefytr


"Eric" wrote in message
...
Hello,

I have a set of workbooks that all have a similar macro in them.
Each
of
these macros contains a string I need to change, but to change it
manually
in each file is time prohibitive - 60+ files.

Given I already have one of these files open with a seperate macro,
from
the
seperate macro, how do I access the module - say Module1, search for
the
line that contains the text needing to be changed, change it and then
close
the file?

I already have the list of files and the loop to go through all of
them.
I
am just unclear on how to change the Module1/Macro text from another
macro.

Any ideas?

Thank you very much in advance.

Eric Pearce