VBA - 从XML代码循环特定的子节点 [英] VBA - Loop specific childnodes from XML code

问题描述

我试图将以下 Xml 删除到Excel工作表中。但是，我只想循环访问特定的子节点，以显示 Name ， PriceEffectiveStart ， PriceEffectiveEnd ，价格和每个索引摘要的货币

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代码

<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代码

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

当前VBA显示输出中的所有节点。我希望它只显示名称， PriceEffectiveStart ， PriceEffectiveEnd ，$ code> $ 和货币。

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.

感谢您的帮助！

推荐答案

您可以在每个 indexPriceSummary node直接得到子元素：

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

这篇关于VBA - 从XML代码循环特定的子节点的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！