PDA

View Full Version : [SOLVED] VBA find replace object error



mrcw
07-20-2017, 10:09 AM
I'm trying to find and replace a specific piece of code in 70 different code objects (macros tied to different worksheets). When I run the code below I get an object error and it highlights 'Set WksName = ws.Name' I got a code from Leith Ross in a separate thread for changing things on individual sheets that helped me out astronomically but now I want to change code on all my sheets and this is my attempt at adapting his code.



Dim Code As String Dim VBComp As Object
Dim VBProj As Object
Dim ws As Worksheet
Dim wb As Workbook
Dim WksName As String

Set wb = ActiveWorkbook
Set ws = wb.ActiveSheet

For Each ws In wb.Worksheets
Set WksName = ws.Name
Set VBProj = Application.VBE.ActiveVBProject
Set VBComp = VBProj.VBComponents(WksName)

With CodeModule
VBComp.Code = .Lines(1, .CountOfLines)
Code=Replace(Code,"find","replace")
.DeleteLines 1, .CountOfLines
.InsertLines 1, Code
End With
ws.visible = xlSheetHidden
Next ws




Thanks in advance for any and all help.

YasserKhalil
07-20-2017, 11:06 AM
Hello
can you attach sample of your workbook?

mrcw
07-20-2017, 11:26 AM
It unfortunately has a large amount of sensitive information... let me see if I can't create a slimmed down copy with false information.

mrcw
07-20-2017, 11:38 AM
I made a separate document that should be all you need. If you can get the "Replace This" line in the macros connected to sheets 1, 2 & 3 to automatically change to "Nice Work" with the code in module 1 you will have figured it out. Make sure to enable Microsoft Visual Basice for Applications Extensibility 5.3 in your references.

Please let me know if you have any follow up questions or if you feel like you're onto something but are getting a different error message.

YasserKhalil
07-20-2017, 12:28 PM
Hello Try this code based on Mr. Leith Ross's code


'Reference : Microsoft Visual Basice for Applications Extensibility 5.3
'----------------------------------------------------------------------


Sub ChangeMacro()
Dim Wb As Workbook
Dim Ws As Worksheet
Dim VBComp As Object
Dim VBProj As Object
Dim Code As String
Dim WksName As String


Set Wb = ActiveWorkbook


For Each Ws In Wb.Worksheets
WksName = Ws.CodeName
Set VBProj = Application.VBE.ActiveVBProject
Set VBComp = VBProj.VBComponents(WksName)


With VBComp.CodeModule
Code = .Lines(1, .CountOfLines)
Code = Replace(Code, "Replace This", "Nice Work!")
.DeleteLines 1, .CountOfLines
.InsertLines 1, Code
End With


'There Is No Way To Hide All The Sheets In The Workbook
'ws.Visible = xlSheetHidden
Next Ws

MsgBox "Done...", 64
End Sub

mrcw
07-20-2017, 12:35 PM
Was exactly what I was looking for, thanks a bunch!

YasserKhalil
07-20-2017, 12:44 PM
You're welcome. Glad I can offer some help
And many thanks for Mr. Leith Ross (Credits go to him ..)

mrcw
07-20-2017, 01:21 PM
Any idea why I would get an "Invalid procedure call or argument" error when I try to take this to my full workbook? I'm trying to find and replace "Z:\Contracts\XYZ\GX Generics\COMLogo.png" with "T:\COMMON\GX Generics\Files for Program\COMlogo.png"

Also tried slimming the find and replace items to "Z:\Contracts\XYZ\" and "T:COMMON" but still no luck

YasserKhalil
07-20-2017, 02:10 PM
Have you tried that line

Code = Replace(Code, "Z:\Contracts\XYZ\GX Generics\COMLogo.png", "T:\COMMON\GX Generics\Files for Program\COMlogo.png")

It is better to refer to the line where you got the error .. bu clicking Debug and copy for us the yellow line

mrcw
07-20-2017, 02:26 PM
I did try that.. unfortunately couldn't debug it for some reason. Here is the error box that popped up.
19822

The really weird part is that it changed everything despite the error code showing up.

YasserKhalil
07-20-2017, 02:56 PM
May be the error appears for the second time .. when the code found nothing to replace ..!!