How to edit an Access module from another vba script [duplicate]

Question

I want to make a VBA script that goes through the directories, opens my database access and will modify a certain character string with another in the modules and Forms code.

I can browse my folders and open my databases, but I don't know how to modify the code.

For now I have this code which allows me to find the character string but not to replace it. :

Dim msACC As Access.Application
Set msACC = GetObject(file.Path)
 
For i = 0 To msACC.Modules.Count - 1
   If (msACC.Modules.Item(i).Find(TextToFind, 0, 0, 100000, 100000)) Then
     'Find it
   End If
Next i
msACC.Quit
Set msACC = Nothing

I find this solution that works for me :

Set re = New RegExp
re.Pattern = TextToFind

Dim msACC As Access.Application
Set msACC = GetObject(file.Path)
For i = 0 To msACC.Modules.Count - 1
    With msACC.Modules.Item(i)
        For j = 1 To .CountOfLines
            If InStr(.Lines(j, 1), TextToFind) > 0 Then
                .ReplaceLine j, re.Replace(msACC.Modules.Item(i).Lines(j, 1), TextToReplace)
            End If
        Next j
    End With    
Next i
msACC.Quit
Set msACC = Nothing

lorenz albert · Accepted Answer · 2023-10-18 07:50:19Z

Use the Module.ReplaceLine Method. And take the info you get from the Module.Find Method.

You need to pass the startLine, startCol, endLine, endLong as variables. Those are byRef parameter and will be "manipulated" by the Find method. This way you get the position of the found string.

Here is an example (This will only change the first occurence of the string in a module):

Sub ReplaceTextInCodeModule(TextToFind As String, TextToReplace As String)
    Dim msACC As Access.Application
    Dim startLine As Long, startCol As Long, endLine As Long, endCol As Long
    Dim curLineText As String
    Dim newLineText As String
    Dim curModule As Module
    Set msACC = Application
    
   
    For i = 0 To msACC.Modules.Count - 1
        Set curModule = msACC.Modules.Item(i)
        If (curModule.Find(TextToFind, startLine, startCol, endLine, endCol)) Then
            If startLine = endLine Then
                curLineText = curModule.Lines(startLine, 1)
                newLineText = Replace(curLineText, TextToFind, TextToReplace)
                curModule.ReplaceLine startLine, newLineText
            End If
        End If
    Next i
    
    
    Debug.Print "Some text to replace"
End Sub

Collectives™ on Stack Overflow

How to edit an Access module from another vba script [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related