VBA Function To Read XML Nodes

Question

I am very new to working with XML and was wondering if anyone could help get me started?

I am trying to write a VBA function that could output an array with just the List Names [ie Array("Recon", "Safety", "Performance")].

I also need to have the functionality to output an array with just a specific "To" node's text. For example, I may call the function to pull the names that are to be CC'ed in reference to the Recon list [ie Array("Jane","Ashley") or Array("Jane;Ashley;") -- whichever output that would make the most sense works for me)

Also, if there is a better way to structure this XML I would be open to changing, as I am writing this from scratch, my VBA userforms will eventually be creating and modifying this file in the end.

<?xml version="1.0" encoding="utf-8"?>
<Settings>
<DistributionLists>
    <List>
        <Name>Recon</Name>
        <TO>John;Bob;Rob;Chris;</TO>
        <CC>Jane;Ashley;</CC>
        <BCC>Brent;</BCC>
    </List>
    <List>
        <Name>Safety Metrics</Name>
        <TO></TO>
        <CC></CC>
        <BCC></BCC>
    </List>
    <List>
        <Name>Performance Report</Name>
        <TO></TO>
        <CC></CC>
        <BCC></BCC>
    </List>
</DistributionLists>

<Presets>
</Presets>

</Settings>

http://stackoverflow.com/questions/11305/how-to-parse-xml-using-vba This may help — 99moorem, Jul 08 '15 at 15:12

score 1 · Accepted Answer · answered Jul 08 '15 at 17:15

Here is an example of getting the To array. Good starting point for you to get the other arrays/nodes.

Function GetToArr()
    Dim XDoc As Object
    Dim xEmpDetails As Object
    Dim xEmployee As Object
    Dim xChild As Object

    Set XDoc = CreateObject("MSXML2.DOMDocument")
    XDoc.async = False
    XDoc.validateOnParse = False
    XDoc.Load ("C:\[path]\test.xml")
    Set items = XDoc.DocumentElement
    Set Item = items.ChildNodes(0)
    Set Item = Item.FirstChild
    Set Item = Item.ChildNodes(1)
    GetToArr = Split(Item.Text, ";")
End Function

Sub TestGetToArr()
    For Each it In GetToArr()
        Debug.Print it
    Next it
End Sub

Result:

John
Bob
Rob
Chris

Thanks! That helped ALOT :) – Chris Newman Jul 09 '15 at 17:44 — Chris Newman, Jul 09 '15 at 17:44

VBA Function To Read XML Nodes

1 Answers1