Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2014-08-02
5
Medium Priority
?
4,025 Views
Last Modified: 2014-08-17
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
0
Comment
Question by:Suk Singh
  • 2
  • 2
5 Comments
 
LVL 46

Expert Comment

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

Accepted Solution

by:
ltlbearand3 earned 2000 total points
ID: 40236624
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
 

Author Comment

by:Suk Singh
ID: 40237554
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
 
LVL 46

Expert Comment

by:aikimark
ID: 40237571
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
 
LVL 20

Assisted Solution

by:ltlbearand3
ltlbearand3 earned 2000 total points
ID: 40238037
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
Viewers will learn the different options available in the Backstage view in Excel 2013.
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 …

572 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