News, links, downloads, tips and tricks on Microsoft Access and related

About Me Search
Name:Alex Dybenko

Location:Moscow, Russia

Wednesday, July 22, 2009

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
    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

Labels: ,


Post a Comment

<< Home