Replace a string in vba code

[Ben_Entrew]

Hi all,

I got following issue: Want to replace a string in my VBA code.

Tried this one, however it doesn't replace the string:

Public Sub ttt()

Dim vbComp As VBComponent
Dim mdl As CodeModule
Dim i As Integer
Dim lin As Variant

For Each vbComp In Application.VBE.ActiveVBProject.VBComponents
Set mdl = vbComp.CodeModule
For i = 1 To mdl.CountOfLines
lin = mdl.Lines(i, 1)
If InStr(1, lin, "X:") Then
lin = Replace(lin, "X:", "\\abcs\de")
End If
Next i
Next vbComp

End Sub

Many things in advance.

Regards,
Ben

 

[theDBguy]

Hi. Just reading your code and got a little confused about something. You're checking if the string contains "L:" but then tries to replace the "X:" part?

 

[Ben_Entrew]

Hi. Just reading your code and got a little confused about something. You're checking if the string contains "L:" but then tries to replace the "X:" part?

Hi,

you're right, just corrected. I was searching for String X.

Rgds,
Ben

 

[arnelgp]

see this thread:
https://stackoverflow.com/questions/865530/replace-module-text-in-ms-access-using-vba

 

[Ben_Entrew]

Hello arnelgp,

unfortunately this doesn't work. I only need to replace a certain portion of a line not the complete one. And I need to go through all modules.

Thanks.

Rgds Ben

 

[isladogs]

One obvious problem.
Your code will search all modules including the one containing that code.
So you need to exclude that module or procedure from the code loop.

From memory you can't just do a simple replace statement on the variant array but I'm unable to check it at the moment

The code looks like its from the examples on VBA Extensibility on Chip Pearson's site http://www.cpearson.com/Excel/vbe.aspx. If not suggest you look at his examples.
If that doesn't help, I can test your code later when I'm at my computer

A code free solution is also available. Use the Deep Search and Replace feature in the free V-Tools add-in. http://www.skrol29.com/us/vtools.php.
Its fast and works well.

 

[Ben_Entrew]

Hi guys, thank you for your help.

I used following now it works:

Sub IterateAllModules()
Dim vnt As Variant
Dim x As Integer
Dim s As String
Dim mdl As Module

With CurrentProject

For Each vnt In .AllModules
s = vnt.Name

Set mdl = Modules(s)

For x = 0 To mdl.CountOfLines

If SearchOrReplace(s, "X:", "\\abcs\de") = True Then


Else

End If

Next x

Set mdl = Nothing

Next vnt

End With
End Sub


Public Function SearchOrReplace(ByVal ModuleName As String, ByVal StringToFind As String, _
Optional ByVal NewString, Optional ByVal FindWholeWord = False, _
Optional ByVal MatchCase = False, Optional ByVal PatternSearch = False) As Boolean

Dim mdl As Module
Dim lSLine As Long
Dim lELine As Long
Dim lSCol As Long
Dim lECol As Long
Dim sLine As String
Dim lLineLen As Long
Dim lBefore As Long
Dim lAfter As Long
Dim sLeft As String
Dim sRight As String
Dim sNewLine As String
Dim x As Integer

Set mdl = Modules(ModuleName)


If mdl.Find(StringToFind, lSLine, lSCol, lELine, lECol, FindWholeWord, _
MatchCase, PatternSearch) = True Then
If IsMissing(NewString) = False Then
' Store text of line containing string.
sLine = mdl.Lines(lSLine, Abs(lELine - lSLine) + 1)
' Determine length of line.
lLineLen = Len(sLine)
' Determine number of characters preceding search text.
lBefore = lSCol - 1
' Determine number of characters following search text.
lAfter = lLineLen - CInt(lECol - 1)
' Store characters to left of search text.
sLeft = Left$(sLine, lBefore)
' Store characters to right of search text.
sRight = Right$(sLine, lAfter)
' Construct string with replacement text.
sNewLine = sLeft & NewString & sRight
' Replace original line.
mdl.ReplaceLine lSLine, sNewLine
End If
SearchOrReplace = True
Else
SearchOrReplace = False
End If

Set mdl = Nothing

End Function


Thank you.

Regards,
Behzat

 

[theDBguy]

Hi guys, thank you for your help.

I used following now it works:
...
Thank you.

Regards,
Behzat

Hi. Glad to hear you got it sorted out. Good luck with your project.

 

[isladogs]

@Ben_Entrew
The approach used in SearchOrReplace is what I was going to suggest (though shouldn't it be called SearchAndReplace?).

However I can't see how the code in IterateAllModules does anything at all as the If ..Else...End If section is blank

For x = 0 To mdl.CountOfLines

If SearchOrReplace(s, "X:", "\\abcs\de") = True Then


Else

End If

Next x

Set mdl = Nothing

What should that part of the code have been?