VBA - Loop specific childnodes from XML code

Question

I'm trying to scrape the following Xml into an Excel sheet. However, I only want to loop through specific childnodes to show the Name, PriceEffectiveStart, PriceEffectiveEnd, Price, and Currency for each index summary.

XML Code

<indexPrices>
     <indexPriceSummary>
         <id>1</id>
         <uri>www.example.com</uri>
      <index>
        <id>3</id>
        <name>Same Day Index</name>
        <uri>www.example.com.xml</uri>
      </index>
      <priceEffectiveStart>2015-06-26</priceEffectiveStart>
      <priceEffectiveEnd>2015-06-26</priceEffectiveEnd>
      <price>
         <amount>2.4806</amount>
         <currency>CAD</currency>
      </price>
      <duration>1</duration>
      <quantityTraded>
        <amount>474</amount>
        <unit>GJ</unit>
        <contractUnit>Day</contractUnit>
      </quantityTraded>
      <numberOfTrades>7</numberOfTrades>
      <settlementState>Settled</settlementState>
      <lastUpdateDate>2015-06-27T02:15:01-06:00</lastUpdateDate>
    </indexPriceSummary>
    <indexPriceSummary>
        <id>1</id>
        <uri>www.example.com.xml</uri>
     <index>
      <id>1</id>
      <name>Same Day Index </name>
      <uri>www.example.com.xml</uri>
     </index>
     <priceEffectiveStart>2015-06-27</priceEffectiveStart>
     <priceEffectiveEnd>2015-06-27</priceEffectiveEnd>
     <price>
         <amount>2.516</amount>
         <currency>CAD</currency>
     </price>
     <duration>1</duration>
     <quantityTraded>
        <amount>251</amount>
        <unit>GJ</unit>
        <contractUnit>Day</contractUnit>
     </quantityTraded>
     <numberOfTrades>50</numberOfTrades>
     <settlementState>Settled</settlementState>
     <lastUpdateDate>2015-06-28T02:15:00-06:00</lastUpdateDate>
   </indexPriceSummary>
</IndexPrices>

VBA Code

Dim xDoc As DOMDocument
Set xDoc = New DOMDocument

xDoc.LoadXML objHTTP.responseText

Dim i As Integer
Dim list As IXMLDOMNodeList
Set list = xDoc.SelectNodes("//indexPrices/indexPriceSummary")

Dim node As IXMLDOMNode
Dim childNode As IXMLDOMNode
Dim price As IXMLDOMNode

For Each node In list
    i = i + 1

    If (node.HasChildNodes) Then
        For Each childNode In node.ChildNodes
             i = i + 1
            Debug.Print childNode.BaseName & " " & childNode.Text
             Worksheets("Sheet1").Cells(i, 1) = childNode.BaseName
             Worksheets("Sheet1").Cells(i, 2) = childNode.Text
        Next childNode
    End If


  Next node

The current VBA shows all nodes in the output. I would like it to show only Name, PriceEffectiveStart, PriceEffectiveEnd, Price, and Currency for each index summary.

Tim Williams · Accepted Answer · 2015-07-14 16:58:43Z

4

You can use xpath on each indexPriceSummary node to get at the child elements directly:

Sub Tester()
    Dim xDoc As DOMDocument
    Set xDoc = New DOMDocument

    ''more code here


    xDoc.LoadXML objHTTP.responseText

    Dim i As Integer
    Dim list As IXMLDOMNodeList
    Set list = xDoc.SelectNodes("//indexPrices/indexPriceSummary")

    Dim node As IXMLDOMNode, nd As IXMLDOMNode
    Dim childNode As IXMLDOMNode
    Dim price As IXMLDOMNode

    i = 4
    For Each node In list
        i = i + 1

        With Sheet1.Rows(i)
            .Cells(1).Value = GetNodeValue(node, "index/name")
            .Cells(2).Value = GetNodeValue(node, "priceEffectiveStart")
            .Cells(3).Value = GetNodeValue(node, "priceEffectiveEnd")
            .Cells(4).Value = GetNodeValue(node, "price/amount")
            .Cells(5).Value = GetNodeValue(node, "price/currency")
        End With

    Next node

End Sub

Function GetNodeValue(node As IXMLDOMNode, xp As String)
    Dim n As IXMLDOMNode, nv
    Set n = node.SelectSingleNode(xp)
    If Not n Is Nothing Then nv = n.nodeTypedValue
    GetNodeValue = nv
End Function

edited Jul 14, 2015 at 16:58

answered Jul 14, 2015 at 16:19

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Nick Sank Over a year ago

@Newd I can't - need 1500 rep

Gregg Burns Over a year ago

Also note childnodes(i) has an index that can be used as an iterator. So you can get the level of branch you want using Set xmlNodes = xmlDoc.SelectNodes("/.../..."). Count the branches: rws = xmlNodes.length then iterate through them in a loop. For Each xNode In xmlNodes ... Set dNode = xNode.ChildNodes(c - 1) then Set dNode = dNode.SelectSingleNode("...") then myArray(r, c) = dNode.nodeTypedValue // I put this as a comment because it's not a direct answer, but people who find this may be very interested in getting a table/array at a certain level with sibling nodes

Collectives™ on Stack Overflow

VBA - Loop specific childnodes from XML code

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related