excel VBA通过GET请求获取欧洲央行汇率的XML节点“时间”

ktca8awb  于 2023-01-10  发布在  其他
关注(0)|答案(1)|浏览(106)

我正在通过GET从https://www.ecb.europa.eu/stats/eurofxref/eurofxref-daily.xml请求ECB的汇率。这些收到的汇率应保存在数据库表中,但前提是这些汇率尚未被请求和存储。XML文件的结构如下:

<gesmes:Envelope xmlns:gesmes="http://www.gesmes.org/xml/2002-08-01" xmlns="http://www.ecb.int/vocabulary/2002-08-01/eurofxref">
<gesmes:subject>Reference rates</gesmes:subject>
<gesmes:Sender>
<gesmes:name>European Central Bank</gesmes:name>
</gesmes:Sender>
<Cube>
<Cube time="2023-01-06">
<Cube currency="USD" rate="1.0500"/>
<Cube currency="JPY" rate="141.30"/>
<Cube currency="BGN" rate="1.9558"/>
<Cube currency="CZK" rate="24.054"/>
<Cube currency="DKK" rate="7.4370"/>
<Cube currency="GBP" rate="0.88475"/>
<Cube currency="HUF" rate="396.96"/>
<Cube currency="PLN" rate="4.6950"/>
<Cube currency="RON" rate="4.9296"/>
<Cube currency="SEK" rate="11.2580"/>
<Cube currency="CHF" rate="0.9864"/>
<Cube currency="ISK" rate="152.90"/>
<Cube currency="NOK" rate="10.8070"/>
<Cube currency="TRY" rate="19.7065"/>
<Cube currency="AUD" rate="1.5590"/>
<Cube currency="BRL" rate="5.6034"/>
<Cube currency="CAD" rate="1.4331"/>
<Cube currency="CNY" rate="7.2045"/>
<Cube currency="HKD" rate="8.2006"/>
<Cube currency="IDR" rate="16468.30"/>
<Cube currency="ILS" rate="3.7361"/>
<Cube currency="INR" rate="86.9103"/>
<Cube currency="KRW" rate="1337.18"/>
<Cube currency="MXN" rate="20.2645"/>
<Cube currency="MYR" rate="4.6237"/>
<Cube currency="NZD" rate="1.6889"/>
<Cube currency="PHP" rate="58.374"/>
<Cube currency="SGD" rate="1.4156"/>
<Cube currency="THB" rate="35.737"/>
<Cube currency="ZAR" rate="18.2092"/>
</Cube>
</Cube>
</gesmes:Envelope>

我需要的是立方体中"时间"的值:检查一下,我是需要保存还是丢弃它们。
要查询和处理的VBA代码如下:

Dim xhr As Object, node As Object
    ' Set of collection for exchance rates
    Set exchangeRates = New Collection

    ' ServerXMLHTTP object for GET requests of exchange rates of ECB
    Set xhr = CreateObject("msxml2.xmlhttp.6.0")
    xhr.Open "GET", "https://www.ecb.europa.eu/stats/eurofxref/eurofxref-daily.xml", False
    xhr.Send
    ' Processing retrieved exchange rates and storing into created collection for further use
    For Each node In xhr.responseXML.SelectNodes("//*[@rate]")

      exchangeRates.Add Conversion.Val(node.GetAttribute("rate")), node.GetAttribute("currency")
      lb_CurrencySelection.AddItem node.GetAttribute("currency")
      
    Next`

如何从父多维数据集中访问节点"time"?我尝试通过SelectNodes访问,但失败了。
提前感谢并问候

rxztt3cl

rxztt3cl1#

如果您已经在访问节点,可以尝试以下操作:

Set nodes = xhr.responseXML.SelectNodes("//*[@rate]")
time = nodes(0).ParentNode.Attributes.getNamedItem("time").Value

For Each node in nodes
    exchangeRates.Add Conversion.Val(node.GetAttribute("rate")), node.GetAttribute("currency")
    lb_CurrencySelection.AddItem node.GetAttribute("currency")
Next node

相关问题