editing code in modules through VB

[mark]

hello.

due to some network drive mapping changes, I may need to
come up with a way to access a significant list of files,
and search all through them, replacing any and all
instance of a drive mapping of "i:\"
to "h:\something\othersomething\"

I can do that easy enough for the contents of the cells in
all sheets, in all files... have that working fine.

But, IF there are vb components that have it in there, I
will need to change that too.

I have looked at Chip Pearson's 'Programming to the VBE',
and have some idea how to get at the code modules, sheet
code, userforms, and class modules (though I really doubt
there are any of them in these files. Nicely done, thank
you very much, Chip.

So now I'm at the point of cycling through the
vbComponents, like this:

**************
For i = 1 To wbFileToChange.VBProject.VBComponents.Count
Step 1

MsgBox wbFileToChange.VBProject.VBComponents(i).Name

Next i
************

(I am pretty sure that that access all components... will
do the form code, the code modules, the sheet code, all in
that loop... I just put that msgbox line in there to see
what I was getting).

In the worksheet contents, I used the cells.replace method
to look for and replace stuff.

What I need to know is what I can put in where the MsgBox
is above, which will search and replace the contents of
the vbCompents.

Suggestions?

Thanks.
Mark

 

[Bernie Deitrick]

Mark,

Try this. Copy the code below into the same module as the rest of your code.
Then, in the code where you are looping through your workbooks, simply add a
line like:

ReplaceItemInAllVBACode ActiveWorkbook

Note that you must pass a valid workbook object to the sub, one that
represents the workbook you are working on. So if you had done something
like

Dim myBook As Workbook
Set myBook = Workbooks.Open .....

then you would use

ReplaceItemInAllVBACode myBook

HTH,
Bernie
MS Excel MVP

Sub ReplaceItemInAllVBACode(myWB As Workbook)

Dim myVBA As VBIDE.VBComponent
Dim myCode As String

For Each myVBA In myWB.VBProject.VBComponents
With myVBA.CodeModule
myCode = .Lines(1, .CountOfLines)
myCode = Replace(myCode, "i:\", "h:\something\othersomething\")
.DeleteLines 1, .CountOfLines
.InsertLines .CountOfLines + 1, myCode
End With
Next myVBA
End Sub

 

[mark]

Try this. Copy the code below into the same module as
the rest of your code.

Thanks, Bernie.

I do have it passing a valid workbook object, as you noted.

It looks like your suggestion will do it.

Thanks.
Mark