How to overwrite data in XML file?

In recent post I talked about converting XML data to Excel table. But what should You do to just overwrite data in XML file, without converting or copying it into Excel?

Intro

In some cases there is no need to extract XML data into more user friendly Excel table format. For the application purpose, where You upload the XML file, You want to stay with original format of data. But still You need to check one or few things to be sure, that everything is correct.

You can do this manually. Open file, find the tag and its value.

Simple.

However, You will not open manually 100 XML files or more and check one by one, when there is possibility of automation.

It’s OK, totally understandable. I got You buddy!

XML sample

I brought here XML code, the same as I prepared recently.

<CATALOG>
	<PRODUCT name="A">
		<FEATURE1>A1</FEATURE1>
		<FEATURE2>A2</FEATURE2>
		<FEATURE3>A3</FEATURE3>
	</PRODUCT>
	<PRODUCT name="B">
		<FEATURE1>B1</FEATURE1>
		<FEATURE2>B2</FEATURE2>
		<FEATURE3>B3</FEATURE3>
	</PRODUCT>
	<PRODUCT name="C">
		<FEATURE1>C1</FEATURE1>
		<FEATURE2>C2</FEATURE2>
		<FEATURE3>C3</FEATURE3>
	</PRODUCT>
</CATALOG>

The case

According to the XML code, imagine situation: You want to change every ‘2’ into ‘4’, in every feature, in every product. Remember that this is just the example.

Let’s do this!

First of all You need to have file path, then create XML object and load this file into object.

xmlfilename = "...\test.xml"

Set xmlfile = CreateObject("Microsoft.XMLDOM")
xmlfile.Load (xmlfilename)

Secondly You have to select node – if You know the structure of the file start with basic level names.

Set ProductNodes = xmlfile.SelectNodes("/CATALOG/PRODUCT")

If You don’t really know how it is leveled or You want to use ChildNodes from the beginning, just leave slash.

Set ProductNodes = xmlfile.SelectNodes("/")

After that the only thing left is to loop through elements. In this case You need 2 loops – firstly loop through products and secondly loop through its features. Features are Products ChildNodes, just take a look at the XML structure above.

For i = 0 To ProductNodes.Length - 1
    For j = 0 To ProductNodes.Item(i).ChildNodes.Length - 1

Remember to start every loop from 0 to the last element minus one. Most of my errors in looping through ChildNodes were caused by forgetting to subtract 1 element (cause of the start from 0!!).

To check the value of the product child node – feature – use .Text property.

To replace every ‘2’ value in features I used Replace function.

Replace(ProductNodes.Item(i).ChildNodes(j).Text, "2", "4")

Key to success

The last thing, but probably most important in this case, is how to make all the changes permanent into the file. The thing is, You just need to save all the changes.

Call xmlfile.Save(xmlfilename)

If You want to overwrite the file just put the same file path from the beginning. If not, change it.

Code

Option Explicit

Sub main()

Dim xmlfile As Object
Dim xmlfilename As String, excelfilename As String
Dim prod_type As String
Dim ProductNodes As Object
Dim i As Long, j As Long

xmlfilename = "...\test.xml"

Set xmlfile = CreateObject("Microsoft.XMLDOM")
xmlfile.Load (xmlfilename)
Set ProductNodes = xmlfile.SelectNodes("/CATALOG/PRODUCT")

For i = 0 To ProductNodes.Length - 1
    For j = 0 To ProductNodes.Item(i).ChildNodes.Length - 1
        If InStr(1, ProductNodes.Item(i).ChildNodes(j).Text, _
            "2") Then
            ProductNodes.Item(i).ChildNodes(j).Text = _
Replace(ProductNodes.Item(i).ChildNodes(j).Text, "2", "4")
        End If
    Next  
Next

Call xmlfile.Save(xmlfilename)

Msgbox "Done"

End Sub

Summary

This was simple code, but worth to describe, I think. Now I hope You know how to overwrite data in XML file without copying, converting or even opening any single XML file. Just remember those few tips I mentioned above and You will manage to do it by yourself.