How to replace text in Macro

You can use this function MacroReplaceText() to replace object name, whatever, in macro with VBA. Run it as:

MacroReplaceText "Macro1","OldForm", "frmMyNewForm"

You can use the same technique to make new macro, just create a text file in proper format, check ~macrotemp.txt file to find this out.

Public Function MacroReplaceText(strMacro As String, _

        strToFind As String, _

        strToReplace As String)

 

    Dim strTempFile As String

    Dim lngPos1 As Long

    Dim intFile As Integer, strLine As String

    Dim strResult As String

 

    strTempFile = Environ("Temp") & "\~macrotemp.txt"

    intFile = FreeFile

 

    If Len(Dir(strTempFile)) > 0 Then _

        Kill strTempFile

 

    'Save to text

    Application.SaveAsText acMacro, strMacro, strTempFile

 

    Open strTempFile For Input As #intFile

    Do Until EOF(intFile)

        If Len(strLine) > 0 Then

            strResult = strResult & vbCrLf & strLine

        End If

        Line Input #intFile, strLine

    Loop

 

    strResult = strResult & vbCrLf & strLine

    Close #intFile

 

    If Len(Dir(strTempFile)) > 0 Then _

        Kill strTempFile

 

    strResult = Replace(strResult, strToFind, strToReplace)

 

    lngPos1 = InStr(1, strResult, "Version")

    If lngPos1 > 0 Then

        Open strTempFile For Output As #intFile

        Print #intFile, Mid(strResult, lngPos1)

        Close #intFile

        Application.LoadFromText acMacro, strMacro, strTempFile

    End If

    If Len(Dir(strTempFile)) > 0 Then _

        Kill strTempFile

 

End Function