Show Buttons
Share On Facebook
Share On Twitter
Share On Google Plus
Share On Linkdin
Share On Reddit
Contact us
Hide Buttons

VBA-Excel: Update XML File

In our ear­lier post we have seen how to Read Data from XML File using in Microsoft Excel. In this tuto­r­ial will teach you about how to update an XML file using Microsoft Excel.

I would rec­om­mend that you should this post before you con­tinue -Read Data from XML File

Now there are var­i­ous way you can update XML file -

  • Update Sin­gle ParentNode
  • Update attribute of a Node
  • Add a new node under exist­ing node
  • Add new Attribute to the Node


Update Sin­gle ParentNode

Set TitleN­ode = oXMLFile.SelectSingleNode(“/catalog/book[0]/title”)

TitleNode.Text = “I am the new Title Here”

Update Single ParentNode

Update Sin­gle ParentNode

Update attribute of a Node

Set oAt­tribute = oXMLFile.SelectSingleNode(“/catalog/book[0][email protected])

oAttribute.Text = “1111111111111111111111111111”

Update attribute of a Node

Update attribute of a Node

Add a new node under exist­ing node

’ select a par­ent node

Set Par­entN­ode = oXMLFile.SelectSingleNode(“/catalog/book[1]”)

’ add a new childNode

Set childNode = oXMLFile.CreateElement(“NewNode”)

childNode.Text = “I am The New NOde Here”

ParentNode.AppendChild (childNode)

Add a new node under existing node

Add a new node under exist­ing node

Add new Attribute to the Node

Add new Attribute to the Node

Set Par­entN­ode = oXMLFile.SelectSingleNode(“/catalog /book[2]/publish_date”)

’ add its attribute

Set new­Chil­dAt­tribute = oXMLFile.CreateAttribute(“Status”)

newChildAttribute.Text = “Very Old Book”

ParentNode.Attributes.SetNamedItem (new­Chil­dAt­tribute)

Add new Attribute to the Node

Add new Attribute to the Node

Com­plete Code:

Sub UpdateXML()
    Call fnUpdateXMLByTags
End Sub

Function fnUpdateXMLByTags()
    Dim mainWorkBook As Workbook
    Set mainWorkBook = ActiveWorkbook
    
    Set oXMLFile = CreateObject("Microsoft.XMLDOM")
    XMLFileName = "C:\Users\Sumit Jain\Google Drive\Excel Blog pics\XML"
    oXMLFile.Load (XMLFileName)
    Set TitleNodes = oXMLFile.SelectNodes("/catalog/book/title/text()")
    Set PriceNodes = oXMLFile.SelectNodes("/catalog/book/price/text()")
    
    'Update Single ParentNode
    
    Set TitleNode = oXMLFile.SelectSingleNode("/catalog/book[0]/title")
    TitleNode.Text = "I am the new Title Here"
    
    'Update attribute of a Node
    Set oAttribute = oXMLFile.SelectSingleNode("/catalog/book[0][email protected]")
    oAttribute.Text = "1111111111111111111111111111"
    
    'Add a new node under existing node
    
    ' select a parent node
    Set ParentNode = oXMLFile.SelectSingleNode("/catalog/book[1]")

    ' add a new childNode
    Set childNode = oXMLFile.CreateElement("NewNode")
    childNode.Text = "I am The New NOde Here"
    ParentNode.AppendChild (childNode)
     
     'Add new Attribute to the Node
     
     Set ParentNode = oXMLFile.SelectSingleNode("/catalog /book[2]/publish_date")

    ' add its attribute
     Set newChildAttribute = oXMLFile.CreateAttribute("Status")
     newChildAttribute.Text = "Very Old Book"
     ParentNode.Attributes.SetNamedItem (newChildAttribute)
    
    oXMLFile.Save (XMLFileName)
    
    End Function


You may also like...

Leave a Reply

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

%d bloggers like this: