• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 721
  • Last Modified:

Search and Replace code in modules, forms & reports using vba

Is there a way to use vba to search and replace strings in modules and code in forms and reports?
0
Emil_Gray
Asked:
Emil_Gray
  • 5
  • 4
  • 2
  • +1
1 Solution
 
Clever_BobCommented:
Do you mean 'replace the code' or  'replace strings using code'?

if 1) Then copy your code into a text editor (notepad) and use the find/replace function

if 2) use as combination of the 'instr' and the 'replace' function

e.g.

If InStr(1, strData, " run out ") > 0 Then  ...

or

astring = replace(astring , "whattoreplace", "replacewiththis")


hope this helps.


0
 
Emil_GrayAuthor Commented:
An example of what I would like to do is have a vba module that opens another module in design view and searches for qoute_ptr and automatically changes it to quote_ptr. The vba module should loop through all modules and code that is in forms and reports and search and replace erroneous or mispelled strings of data. I don't want to have to copy the code into notepad. There is a manual search and replace already in Access modules. I want to be able to type a string such as qoute_ptr and quote_ptr and have Access loop through all the code in the database and replace the one with the other.
0
 
Emil_GrayAuthor Commented:
The thought occurs to me that maybe VBScript might be a solution. Alas, I am not up to speed on that process.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Clever_BobCommented:
As far as I'm aware, there is no way in VBA to reference itself in this way.

You could certainly do it in VBScript (and alter other .vbs or .asp files for example) but the files that you want to alter are part of MS Access and therefore they are compiled.

So I'm afraid that your only option will be to copy/paste ... change ..copy/paste again but that is obviously not very convenient!
0
 
Arthur_WoodCommented:
there is a commercial product (Speed Ferret  http://www.speedferret.com/speedferret.html) thast can do what you are asking.

AW
0
 
rockiroadsCommented:
there is some VBA u can use, but I cant find any properties to update. You can delete lines and add lines

e.g.


Public Sub FindForm(ByVal sFormName As String, ByVal sOldText As String)

    Dim Frm As Form
    Dim Module As Module
   
    DoCmd.OpenForm sFormName, acDesign, , , , acHidden
    Set Frm = Forms(sFrmName)
    Set Module = Frm.Module
   
    With Module
        If .Find(sOldText, 0, 0, .CountOfLines, 0) > 0 Then
            MsgBox "Found " & sOldText
        Else
            MsgBox "Cannot Found " & sOldText
        End If
    End With
    Set Frm = Nothing: Set Module = Nothing
    DoCmd.Close acForm, sFrmName, acSaveYes

End Sub

Public Sub FindModule(ByVal sModuleName As String, ByVal sOldText As String)

    Dim Module As Module
   
    DoCmd.OpenModule sModuleName
    Set Module = Modules(sModuleName)
   
    With Module
        If .Find(sOldText, 0, 0, .CountOfLines, 100) > 0 Then
            MsgBox "Found " & sOldText
        Else
            MsgBox "Cannot Found " & sOldText
        End If
    End With
    Set Module = Nothing
    DoCmd.Close acModule, sModuleName

End Sub


Public Sub FindText()
    FindModule "Module1", "hello"
End Sub

0
 
Emil_GrayAuthor Commented:
Well, who wudda guessed it. There is a module in the VBA help file that almost does what I want. I am going to post it here for those who want to tinker with it. I have run it and it works! Since rockiroads has some code here that I can incorporate into my little problem I am going to award him the 500 points.

Thanks rockiroads and everyone else who contributed. Here is the code from Access its' own self!

Option Compare Database
Option Explicit

Function FindAndReplace(strModuleName As String, _
    strSearchText As String, _
    strNewText As String) As Boolean
    Dim mdl As Module
    Dim lngSLine As Long, lngSCol As Long
    Dim lngELine As Long, lngECol As Long
    Dim strLine As String, strNewLine As String
    Dim intChr As Integer, intBefore As Integer, _
        intAfter As Integer
    Dim strLeft As String, strRight As String
    ' Open module.
    'DoCmd.OpenModule strModuleName ' originally this line was active but it didn't work so I rmk it and now the mod wks.
    ' Return reference to Module object.
    Set mdl = Modules(strModuleName)
    ' Search for string.
    If mdl.Find(strSearchText, lngSLine, lngSCol, lngELine, _
        lngECol) Then
        ' Store text of line containing string.
        strLine = mdl.Lines(lngSLine, Abs(lngELine - lngSLine) + 1)
        ' Determine length of line.
        intChr = Len(strLine)
        ' Determine number of characters preceding search text.
        intBefore = lngSCol - 1
        ' Determine number of characters following search text.
        intAfter = intChr - CInt(lngECol - 1)
        ' Store characters to left of search text.

        strLeft = Left$(strLine, intBefore)
        ' Store characters to right of search text.
        strRight = Right$(strLine, intAfter)
        ' Construct string with replacement text.
        strNewLine = strLeft & strNewText & strRight
        ' Replace original line.
        mdl.ReplaceLine lngSLine, strNewLine
        FindAndReplace = True

    Else
        MsgBox "Text not found."
        FindAndReplace = False
    End If
Exit_FindAndReplace:
    Exit Function

Error_FindAndReplace:

MsgBox Err & ": " & Err.Description
    FindAndReplace = False
    Resume Exit_FindAndReplace
End Function



0
 
rockiroadsCommented:
Cool, that works for modules. fine, looks like its easy to adapt to the forms as well
nice find
0
 
Emil_GrayAuthor Commented:
When I get a really good version working I'll post it. It will take a while. I'm going to fix it to loop through all modules and forms. Am thinking maybe code in reports can be incorporated as well.

Why does Microsoft keep stuff like this hidden so well?
0
 
rockiroadsCommented:
if this can be done with forms

Set Frm = Forms(sFrmName)
Set Module = Frm.Module


perhaps it can be done with reports,

if u get it working, perhaps u can market it like Arthur's commerical find :)
Might be worth a thought

0
 
Emil_GrayAuthor Commented:
I just might do that. That is a worthwhile project.
0
 
rockiroadsCommented:
Can I get some free shares if your project takes off :)
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 4
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now