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

Hi,

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

wrdDoc.Select

With wrdApp.Selection.Find
    .ClearFormatting
    .Replacement.ClearFormatting
    .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
Suk SinghAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

aikimarkCommented:
please post representative sample(s) of your XML and some examples of what you want to replace (from/to) within the XML documents.
0
ltlbearand3Commented:
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

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Suk SinghAuthor Commented:
Hi,

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.
0
aikimarkCommented:
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?
0
ltlbearand3Commented:
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 myproxy.com with yourproxy.com 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, "myproxy.com", "yourproxy.com", vbTextCompare)

' Save it to the temp file
objNewFile.Write strNewText

' Close Both Files
objTextFile.Close
objNewFile.Close

' 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 http://myproxy.com.  [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='http://myproxy.com']/proxy") 

' Check if we found a match
if Not(objXMLElem) is nothing Then
	' Change the value
	objXMLElem.Text = "http://yourproxy.com"
	
	' Save the New File
	objXMLDoc.Save "C:\file.xml"
End if

msgbox "Done"

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.