How can I Find/Replace multiple strings in an xml

Question

I have about 600 different text strings that I need to replace within an XML file (I am using notepad++ though I can use other programs too if this would accomplish the task). The text changes are listed in a separate excel file. Is there a way that I can run a script or command to find/replace all of the strings in one shot, without having to do each one individually?

Thank you

Answer 1

You could use some simple VBA to do the job from within your Excel s/s. Call the below Sub by passing in the find-replace range e.g. from the Excel VBA Immediate window (access using Alt+F11 for the VBA editor then View -> Immediate):

ReplaceXML Range("A1:B600")

Assuming A1:B600 contains the 600 find-replace strings.

After defining the below in a module (Insert -> Module from within the VBA editor (Alt+F11) ):

Option Explicit ' Use this !

Public Sub ReplaceXML(rFindReplaceRange as Range) ' Pass in the find-replace range

    Dim sBuf As String
    Dim sTemp As String
    Dim iFileNum As Integer
    Dim sFileName As String
    Dim i as Long

    ' Edit as needed
    sFileName = "C:\filepath\filename.xml"

    iFileNum = FreeFile
    Open sFileName For Input As iFileNum

    Do Until EOF(iFileNum)
        Line Input #iFileNum, sBuf
        sTemp = sTemp & sBuf & vbCrLf
    Loop

    Close iFileNum

    ' Loop over the replacements
    For i = 1 To rFindReplaceRange.Rows.Count
        If rFindReplaceRange.Cells(i, 1) <> "" Then
            sTemp = Replace(sTemp, rFindReplaceRange.Cells(i, 1), rFindReplaceRange(i, 2))
        End If
    Next i

    ' Save file

    iFileNum = FreeFile

    ' Alter sFileName first to save to a different file e.g.
    sFileName = "C:\newfilepath\newfilename.xml"
    Open sFileName For Output As iFileNum

    Print #iFileNum, sTemp

    Close iFileNum

End Sub