Mısra GÖZ

Data Exchange between VBA and XML

It is pretty easy to exchange data between Excel & VBA and XML file. XML files are one of the most common files other than Txt and CSV. It is easier to read non-hierarchical data files (XML or JSON). You can read the rows and columns differently in the code line. It is not that easy to check data with XML (and JSON.) There are parent-child relations between the records in the templates and the number of underlying nodes can be changed corresponding to the table data that contain a stable number on columns. This has been separated with a certain delimiter.

Fortunately, we can easily exchange data between Excel & VBA and XML file by using the MSXML2.DOMDocument object in VBA.
But, as always, let’s start with a short introduction concerning how to create a while before we deep into the examples of a structure in XML.

Microsoft Excel, makes it easy to import Extensible Markup Language (XML) data created on out databases and applications, pair XML objects coming from XML schema with the workbook cells and export XML data to interact with out databases and applications. You can think of it as these XML features turns Office Excel in to a XML data file creater that works with a familiar user interface.

Now let’s take a look at this VBA & XML.

Upload XML File to VBA

The MSXML2.DomDocument object enables you to access XML structure easily and provides movement between any required node and/or quality with the XML structure. Let’s take a look at the example below.

Sub PEAKUP()
 Dim Xml As Object, root As Object
 Set Xml = CreateObject("MSXML2.DOMDocument")
 Xml.async = False: Xml.validateOnParse = False
 Xml.Load (ThisWorkbook.Path & "\etf.xml")
 Set root = Xml.DocumentElement End Sub

XML DOM Nodes in VBA

XML file will provide you with the root of all the DOM(XDoc.DocumentElement Model).

Each DocumentElement (XML DOM node) facilitated the node references below:

parentNode	Parent node, the top node in the DOM hierarchy
firstChild	First child node, the first child node in the DOM hierarchy
lastChild	Last child node, the last child node in the DOM hierarchy
childNodes	All child nodes of the node, all nodes in the DOM hierarchy
nextSibling	The same-level node in the same master node in the DOM hierarchy, i.e. the next sibling node.
previousSibling	The same-level node in the same master node in the DOM hierarchy, i.e. the previous sibling node.

All the references below help you to access the data in XML DOM.

childNodes

Let’s take the first node as an example and have the text content as MsgBox. We usually use ChildNodes while exploring nodes in XML DOM.

Sub PEAKUP()
    Dim XDoc As Object
    Set XDoc = CreateObject("MSXML2.DOMDocument")
    XDoc.async = False: XDoc.validateOnParse = False
    XDoc.Load (ThisWorkbook.Path & "\etf.xml")
    Set lists = XDoc.DocumentElement
    Set ilkdugum = lists.FirstChild
    MsgBox ilkdugum.Xml
    MsgBox ilkdugum.Text
    Set XDoc = Nothing
End Sub

You can download this file and test the codes below.

Now that we’ve got all the basic information, we can write the all content of XML to Excel with these codes.

Sub PEAKUP()
    Dim XDoc As Object
    Set XDoc = CreateObject("MSXML2.DOMDocument")
    XDoc.async = False: XDoc.validateOnParse = False
    XDoc.Load (ThisWorkbook.Path & "\etf.xml")
    Set lists = XDoc.DocumentElement
    For Each satirNode In lists.ChildNodes
        For Each alandNode In rowNode.ChildNodes
            a = a + 1
            Cells(a, 1) = "[" & alanNode.BaseName & "] = [" & alanNode.Text & "]"
        Next alanNode 
    Next satirNode 
    Set XDoc = Nothing
End Sub

You’ll see that you can do this action with the examples below as well.

XML Attributes in VBA

You can get the ^attribute values states as current attribute in the XML Nodes. Let’s assume that you have an XML structure like this:

<?xml version="1.0" encoding="utf-8"?>
<AnaDugum>
    <Liste>
        <Adi attribute="PEAKUP">ORG</Adi>

You can get the attributes easily with XPath as shown below.

Set adialani = XDoc.SelectNodes("//AnaDugum/Liste[0]/Adi")
MsgBox adialani(0).Attributes(0).Text

Create an XML File

ub XML_Belge_Olusturma()
    Dim XDoc As Object, root As Object, elem As Object, rel As Object
    Set XDoc = CreateObject("MSXML2.DOMDocument")
    Set root = XDoc.createElement("Root")
        XDoc.appendChild root
    Set elem = XDoc.createElement("Child")
        root.appendChild elem
    Set rel = XDoc.createAttribute("Oznitelik")
        rel.NodeValue = "PEAKUP"
        elem.setAttributeNode rel
    XDoc.Save ThisWorkbook.Path & "\yeni.xml"
End Sub

In addition to the information above, I want to share a few code blocks that can be used to Import Data from an XML file, Search and Delete.

To list all the data in the XML file:

Sub XML_Dosyasını_Okuma()
    Dim Etf As Object, ayır() As String, i As Integer
    Set Etf = CreateObject("MSXML2.DOMDocument")
    Etf.async = False
    If Etf.Load(ThisWorkbook.Path & "\etf.xml") Then
        ayır = Split(Etf.Text, " ")
        For i = 0 To UBound(ayır)
            Cells(i + 1, 1) = ayır(i)
        Next i
    End If
    Set Etf = Nothing: Erase ayır: i = Empty
End Sub

To list all the data in the management Node in the XML file:

DefObj I, N, R
Sub XML_Dosyasından_Veri_Alma()
    Dim i As Integer
    Set Etf = CreateObject("MSXML2.DOMDocument")
    Etf.async = False
    Etf.Load (ThisWorkbook.Path & "\etf.xml")
    Set Nod = Etf.SelectNodes("//yonetim/*")
    For Each Item In Nod
        i = i + 1
        Cells(i, 1) = Item.Text
    Next Item
    Set Etf = Nothing: Set Nod = Nothing
    Set Item = Nothing: i = Empty
End Sub

To list the information of the person named Murat in the management Node in XML file:

DefObj I, N, R
Sub XML_Dosyasından_Veri_Alma()
    Dim i As Integer
    Set Etf = CreateObject("MSXML2.DOMDocument")
    Etf.async = False
    Etf.Load (ThisWorkbook.Path & "\etf.xml")
    Set Nod = Etf.SelectNodes("//yonetim[ad='Murat']")
    For Each Item In Nod
        i = i + 1
        Cells(i, 1) = Item.Text
    Next Item
    Set Etf = Nothing: Set Nod = Nothing
    Set Item = Nothing: i = Empty
End Sub

To list the information of people whose City name is Istanbul in management node in XML file:

DefObj I, N, R
Sub XML_Dosyasından_Veri_Alma()
    Dim i As Integer
    Set Etf = CreateObject("MSXML2.DOMDocument")
    Etf.async = False
    Etf.Load (ThisWorkbook.Path & "\etf.xml")
    Set Nod = Etf.SelectNodes("//yonetim[sehir='Istanbul']")
    For Each Item In Nod
        i = i + 1
        Cells(i, 1) = Item.Text
    Next Item
    Set Etf = Nothing: Set Nod = Nothing
    Set Item = Nothing: i = Empty
End Sub

To list the information of the person named Emre with the SelectsingleNode method in management node in XML file:

DefObj I, N, R
Sub XML_Dosyasından_Veri_Alma()
    Dim i As Integer
    Set Etf = CreateObject("MSXML2.DOMDocument")
    Etf.async = False
    Etf.Load (ThisWorkbook.Path & "\etf.xml")
    Set Nod = Etf.SelectSingleNode("//yonetim[ad='Emre']")
    Range("A1").Value = Nod.Text
    Set Etf = Nothing: Set Nod = Nothing
    Set Item = Nothing: i = Empty
End Sub

To remove “Murat OSMA” in the site_management group from the list and save as new.xml in the management node in XML file:(you can write on the old file too).

DefObj I, N, R
Sub Nod_Silme()
    Set Etf = CreateObject("MSXML2.DOMDocument")
    Etf.async = False
    Etf.Load (ThisWorkbook.Path & "\etf.xml")
    Set Nod = Etf.SelectNodes("//yonetim[ad='Murat']")
    For Each Item In Nod
        Item.ParentNode.RemoveChild Item
    Next Item
    Etf.Save ThisWorkbook.Path & "\yeni.xml"
End Sub