Be the first user to complete this post

  • 0
 Add to List

VBA-Excel: Update XML File

In our earlier post we have seen how to Read Data from XML File using in Microsoft Excel. In this tutorial will teach you about how to update an XML file using Microsoft Excel.

I would recommend that you should this post before you continue -Read Data from XML File

Now there are various way you can update XML file -

  • Update Single ParentNode
  • Update attribute of a Node
  • Add a new node under existing node
  • Add new Attribute to the Node


Update Single ParentNode

Set TitleNode = oXMLFile.SelectSingleNode("/catalog/book[0]/title")

TitleNode.Text = "I am the new Title Here"

Update Single ParentNode
Update Single ParentNode

Update attribute of a Node

Set oAttribute = oXMLFile.SelectSingleNode("/catalog/book[0]/@id")

oAttribute.Text = "1111111111111111111111111111"

Update attribute of a Node
Update attribute of a Node

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 a new node under existing node
Add a new node under existing node

Add new Attribute to the Node

'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)

Add new Attribute to the Node
Add new Attribute to the Node

Complete 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]/@id")
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

Also Read:

  1. Excel-VBA : Send Unique Images Embedded to Mail Body, With Every Mail From MS Outlook using Excel.
  2. VBA-Excel: Appending Text to Existing Word Document - at Beginning
  3. VBA-Excel: Working with Microsoft Word
  4. VBA-Excel — AttachmentFetcher — Download all the Attachments from All the Mails of Specific Subject in Microsoft Outlook .
  5. VBA-Excel: Format the Existing Table in a Word document