Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
editing code in modules through VB
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
editing code in modules through VB
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
editing code in modules through VB
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Where 2 place the code? (Worksheet Codes Vs. Modules) | Excel Discussion (Misc queries) | |||
Delete Code Modules Programatically | Excel Discussion (Misc queries) | |||
Remove code from all modules closes Excel | Excel Programming | |||
Strip Modules from Workbook - Code Organization | Excel Programming | |||
Scope of variable includes all Form _and_ Code modules?? | Excel Programming |