Create a VBScript to search and replace words in a XML document

Posted on 2014-08-02
Last Modified: 2014-08-17

I am hoping someone can help me. I am trying to create a VBScript to search an XML document and change some numbers. Basically it will search through the whole document and when it finds the number it will replace this with another number, it will then save and close.
I have been able to create this with word but I am struggling to replicate this with XML.
My word script at the bottom. Any help would be greatly appreciated. Thanks

Option Explicit
'declare and instaciate wrdApp
Dim wrdApp: Set wrdApp = WScript.CreateObject("Word.Application")
'declare wrdDoc
Dim wrdDoc

Dim wdReplaceAll

'Open the document
Set wrdDoc = wrdApp.Documents.Open("C: \Test.docx")

'set the value for the replace "constant"
wdReplaceAll = 2


With wrdApp.Selection.Find
    .Text = "aaa"
    .Replacement.Text = "bbb"
    .Forward = True
    .Wrap = 1
    .Format = False
    .MatchCase = False
    .MatchWholeWord = False
'the Replace argument is the 11'th argument
    .Execute , , , , , , , , , , wdReplaceAll
End With

'clean up
Set wrdApp = Nothing
Set wrdDoc = Nothing
Question by:Suk Singh
    LVL 44

    Expert Comment

    please post representative sample(s) of your XML and some examples of what you want to replace (from/to) within the XML documents.
    LVL 20

    Accepted Solution

    Some of this depends on how complicated you need to be on replacing the phone number.  For example, do we have to look for a certain node in the XML or can we just do a brute force replace?  An XML document, in some ways, is just a formatted text document.  If we can ignore the XML tags and just look for and replace the phone number, it makes the vbscript pretty simple.  For example this script will search for the text of 888-777-1234 and replace it with 888-777-4321 in any text file (including xml).

    Dim objFSO, objTextFile, strText, strNewText
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objTextFile = objFSO.OpenTextFile ("c:\file.txt", 1) ' 1 is for Reading
    strText = objTextFile.ReadAll
    strNewText = Replace(strText, "888-777-1234", "888-777-4321", vbTextCompare)
    msgbox strNewText

    Open in new window


    Author Comment

    by:Suk Singh

    Thanks for the response. Basically I would like to replace the proxy address which is stored in an XML file. I would like to search for that specific address within the XML document and replace it with another address.
    LVL 44

    Expert Comment

    Please post a representative sample XML file and a string you would want to use in the replacement process.

    Will the proxy address be the same for all the XML files?
    LVL 20

    Assisted Solution

    As Aikimark suggests, this will be easier if you post a sample file.  I realized i did not read your question close enough the first time.  You will have to adjust this scripts to fit your needs as we do not have enough information to fill in all the gaps.

    You can just use the file system object to search and replace the file.  However, saving it back out creates a little overhead.  Here is sample script that will ready an XML file, replace with and save the file back out.  You will need to update the file name.  If you need to do this on multiple files, we will need more details.

    Dim objFSO, objTextFile, strText, strNewText, objNewFile
    Dim strOriginalFile, strTempFile
    ' ***** change this value to be the value of your file *******
    strOriginalFile = "c:\file.xml"
    ' I would also change this path to match or we can add more code to use the temp directory
    strTempFile = "c:\temp.xml"
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    ' Create the Temp File
    Set objNewFile = objFSO.CreateTextFile(strTempFile)
    ' Open the Original File
    Set objTextFile = objFSO.OpenTextFile (strOriginalFile, 1) ' 1 is for Reading
    strText = objTextFile.ReadAll
    ' Copy in the New TextStream.AtEndOfLine
    strNewText = Replace(strText, "", "", vbTextCompare)
    ' Save it to the temp file
    objNewFile.Write strNewText
    ' Close Both Files
    ' Delete the Original File and rename the temp to the original
    objFSO.DeleteFile strOriginalFile
    objFSO.MoveFile strTempFile, strOriginalFile
    ' Tell the user we are done.
    msgbox "Done"

    Open in new window

    Now if we know more about xml structure, it would be easier to use the Dom Document object.  This will open up an xml file search for a node called RootNode and find a child node named proxy with a value of  [Note the node names are case sensitive].  It will then change the value and save it off.

    Set objXMLDoc = CreateObject("Msxml2.DOMDocument") 
    objXMLDoc.Load "C:\file.xml"
    ' Load the XML Document
    Set objXMLElem = objXMLDoc.selectSingleNode("//RootNode[proxy='']/proxy") 
    ' Check if we found a match
    if Not(objXMLElem) is nothing Then
    	' Change the value
    	objXMLElem.Text = ""
    	' Save the New File
    	objXMLDoc.Save "C:\file.xml"
    End if
    msgbox "Done"

    Open in new window


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    This very simple solution applies to a narrow cross-section of the "needs to close" variety. In this case, the full message in Event Viewer was in applog, Event ID 1000: Faulting application iexplore.exe, version 8.0.6001.18702, faulting module …
    User Beware!  This is a rather permanent solution to removing your email from an exchange server.  The only way to truly go back is to have your exchange administrator restore your mailbox from backups.  This is usually the option of last resort.  A…
    Viewers will learn how to maximize accessibility options in an Excel workbook for users with accessibility issues.
    The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

    794 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now