Stödet för XML och XML-filer i VBA

XML-filer är en av de vanligaste datafilerna i likhet med text- resp kommaseparerade (CSV) filer. Att läsa filer som inte är hierarkiska (som XML eller JSON) är relativt enkelt. Man kan läsa rad för rad och processa varje koloumn separat. Om man då har XML (eller JSON) är situationen lite annorlunda eftersom det finns en hierarki (child-parent-forhållanden) i inbyggd i datastrukturen mellan posterna i datat). Antalet underliggande noder kan variera i motsats till tabulära data som vanligtvis har ett konstant antal kolumner, separerade med en given avgränsare.

Lyckligtvis kan vi använda VBAs objekt MSXML2.DOMDocument. Oavsett vilket, låt oss börja med en kort introduktion innan vi dyker ner i olika exempel.

Läsa in ett XML-dokument med VBA

Med hjälp av objektet MSXML2.DOMDocument kan vi navigera runt i XML-strukturen och läsa ut önskad XML-nod/atribut.

I nedanstående exempel inleder vi med att ladda XML-dokumentet. Notera att vi läser in dokumentet synkront samt att ingen validering sker.

Sub TestXML()
    Dim XDoc As Object, root as Object
     
    Set XDoc = CreateObject("MSXML2.DOMDocument")
    XDoc.async = False: XDoc.validateOnParse = False
    XDoc.Load (ThisWorkbook.Path & "\test.xml")
    Set root = XDoc.DocumentElement
    '... 
End Sub

Alternativt kan vi läsa in dokumentet från en string:

Sub TestXML()
    Dim XDoc As Object, root as Object
     
    Set XDoc = CreateObject("MSXML2.DOMDocument")
    XDoc.async = False: XDoc.validateOnParse = False
    XDoc.LoadXML ("<root><child></child></root>")
    Set root = XDoc.DocumentElement
    '... 
End Sub

Sådärja, nu har vi läst in dokumentet i MSXML2.DOMDocument-objektet. I och med detta kan vi enkelt navigera runt bland dess element!

XML-DOM-noder och VBA

XML-dokumentet ger oss direkt roten till hela DOM-strukturen (av typen XDoc.DocumentElement). Varje DocumentElement har följande metoder:

  • parentNode [XDoc.DocumentElement] – föräldranoden (parent), en nod högre i hierarkin
  • firstChild [XDoc.DocumentElement] – översta/första barnnoden (first child), den första lägre noden i hierarkin
  • lastChild [XDoc.DocumentElement] – understa/understa barnnoden (first child), den första lägre noden i hierarkin
  • childNodes [Array of type XDoc.DocumentElement] – alla undernoder (child nodes) till den betraktade noden
  • nextSibling [XDoc.DocumentElement] – nästa element på samma nivå (som det man utgår ifrån, d v s har samma parent-node) när man itererar över en DOM-struktur
  • previousSibling [XDoc.DocumentElement] – föregående element på samma nivå (som det man utgår ifrån) när man itererar över en DOM-struktur

Child-noder

Låt oss börja med första listning av ett XML-dokument och skriva ut dess innehåll. Med hjälp av ChildNodes navigerar vi med lätthet!

Sub TestXML()
    Dim XDoc As Object
     
    Set XDoc = CreateObject("MSXML2.DOMDocument")
    XDoc.async = False: XDoc.validateOnParse = False
    XDoc.Load (ThisWorkbook.Path & "\test.xml")
     
    'Get Document Elements
    Set lists = XDoc.DocumentElement
     
    'Get first child ( same as ChildNodes(0) )
    Set getFirstChild = lists.FirstChild
    'Print first child XML
    Debug.Print getFirstChild.XML
    'Print first child Text
    Debug.Print getFirstChild.Text
 
    Set XDoc = Nothing
End Sub

Här är resultatet:

    'Print first child XML
    <List>
        <Name>Recon</Name>
        <TO>John;Bob;Rob;Chris</TO>
        <CC>Jane;Ashley</CC>
        <BCC>Brent</BCC>
    </List>
'Print first child Text
Recon John;Bob;Rob;Chris Jane;Ashley Brent

Navigera genom hela XML med VBA

När vi nu har grunderna så låt oss skriva ut hela dokumentet tillsammans med basnamnen (nod-namnen / node names):

Sub TestXML()
    Dim XDoc As Object
     
    Set XDoc = CreateObject("MSXML2.DOMDocument")
    XDoc.async = False: XDoc.validateOnParse = False
    XDoc.Load (ThisWorkbook.Path & "\test.xml")
     
    'Get Document Elements
    Set lists = XDoc.DocumentElement
     
    'Traverse all elements 2 branches deep
    For Each listNode In lists.ChildNodes
        Debug.Print "---Email---"
        For Each fieldNode In listNode.ChildNodes
            Debug.Print "[" & fieldNode.BaseName & "] = [" & fieldNode.Text & "]"
        Next fieldNode
    Next listNode
     
    Set XDoc = Nothing
End Sub

Här är resultatet:

---Email---
[Name] = [Recon]
[TO] = [John;Bob;Rob;Chris]
[CC] = [Jane;Ashley]
[BCC] = [Brent]
---Email---
[Name] = [Safety Metrics]
[TO] = [Tom;Casper]
[CC] = [Ashley]
[BCC] = [John]
---Email---
[Name] = [Performance Report]
[TO] = [Huck;Ashley]
[CC] = [Tom;Andrew]
[BCC] = [John;Seema]

Genom att använda ovanstående grundtekniker kan vi med lätthet navigera runt i dokumentet. Förfarandena kräver iofs mycket kodande och det finns bättre / enklare sätt i och med DOMDocument objekt XPath.

XPath och VBA

Istället för att navigera runt bland elementen och noderna i det aktuella XML-dokumentet med .ChildNodes/.FirstNode/.NextChild-egenskaperna kan vi använda XPath. XPath är i likhet med SQL ett frågespråk som används för att välja ut vissa noder i ett XML-dokument och representeras av en enkel string. Med hjälp av den kan man extrahera noder (0 eller fler, n*) som matchar en given XPath query.

Sub TestXML()
    Dim XDoc As Object
     
    Set XDoc = CreateObject("MSXML2.DOMDocument")
    XDoc.async = False: XDoc.validateOnParse = False
    XDoc.Load (ThisWorkbook.Path & "\test.xml")
     
    Set lists = XDoc.SelectNodes("//DistributionLists/List")
 
    Set XDoc = Nothing
End Sub
Exempel 1: Hämta ut alla Lists

    Set toFields = XDoc.SelectNodes("//DistributionLists/List/TO")
Exempel 2: Hämta ut alla fält av typen TO

    Set firstNameField = XDoc.SelectNodes("//DistributionLists/List[0]/Name")
 
    Set lastNameField = XDoc.SelectNodes("//DistributionLists/List[2]/Name")
Exempel 3: Hämta första och sista fält av typen Name

    Set listChildrenField = XDoc.SelectNodes("//DistributionLists/List/*")
Exempel 4: Hämta alla barnelement (child nodes) till List (Name,TO,CC,BCC)

XML-attribut i VBA

Låt oss då ta ett sista exempel, attribut (attribute).Om vi modifierar ovanstående XML till att även innehålla ett attribut attribute:

<?xml version="1.0" encoding="utf-8" ?>
<DistributionLists>
    <List>
    <Name attribute="some">Recon</Name>

Genom att använda XPath (eller genom att navigera igenom DOM-trädet) kan vi enkelt extrahera attributet som i nedanstående exempel:

Set firstNameField = XDoc.SelectNodes("//DistributionLists/List[0]/Name")
Debug.Print firstNameField(0).Attributes(0).Text
'Result: "some"

Skapa XML-dokument

Inte heller själva dokumentskapandet bjuder på några större hinder:

Dim XDoc As Object, root As Object, elem As Object
Set XDoc = CreateObject("MSXML2.DOMDocument")
Set root = XDoc.createElement("Root")
XDoc.appendChild root
  
'Add child to root
Set elem = XDoc.createElement("Child")
root.appendChild elem
     
'Add Attribute to the child
Dim rel As Object
Set rel = XDoc.createAttribute("Attrib")
rel.NodeValue = "Attrib value"
elem.setAttributeNode rel
     
'Save the XML file
XDoc.Save "C:\my_file.xml"
Annonser