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?

How to overwrite data in XML file?

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.

Author: Tomasz Płociński

I'm very advanced in VBA, Excel, also easily linking VBA with other Office applications (e.g. PowerPoint) and external applications (e.g. SAP). I take part also in RPA processes (WebQuery, DataCache, IBM Access Client Solutions) where I can also use my SQL basic skillset. I'm trying now to widen my knowledge into TypeScript/JavaScript direction.

9 thoughts on “How to overwrite data in XML file?”

  1. Thanks for the great article, it’s really helpful.
    Would like to check if any way I can locate the specific attribute value then amend it’s child text?

    Below is the part of the data I have in xml, it has many xCoord & yCoord (more than 1K), and I already have idea which X & Y coordinate I want to change their defect, not sure if any quick way other than run from line 1 to end?

    Thanks a lot for the help.

    0000

    1. Hello,
      I would go from first to the last, but maybe there is faster approach to get specified tag based on its property 🙂

  2. Hi,

    Thank you for making this article.

    I have multiple large sized .XML files in a folder that requires rows (or nodes) being deleted if a specific string is found in a specific column (if the XML is viewed in a dataframe perspective).

    Hope you can assist.

  3. How can I adapt for this code instead of txt to be xml and in utf-8:

    Set fso = CreateObject(“Scripting.FileSystemObject”)
    Set Fileout = fso.CreateTextFile(“G:\Mi unidad\PC\Documentos\” & nombre_txt, True)
    Fileout.WriteLine (cadena)
    Fileout.Close

    1. Have You tried to include the file name & xml extension inside the CreateTextFile function?

  4. if name_txt=name.xml but in the output I don’t know if I need to add that it has utf-8 encoding

Leave a Reply

Your email address will not be published. Required fields are marked *