问在VBA中获取XML响应中的值
EN

Stack Overflow用户

提问于 2013-12-19 20:50:29

回答 1查看 1.9K关注 0票数 2

我使用GET请求从web服务获取XML响应。我正在尝试解析响应并在VBA表单中显示响应的一部分。我认为我能很好地处理VBA组件，但是XML语法给我带来了很多问题。这是到目前为止我在VBA中的sub：

Sub GetCANSIM()
Dim XMLRequest As New XMLHTTP
Dim objXML As MSXML2.DOMDocument
Dim point As IXMLDOMNode
Dim value As Variant
Dim lngYear As Long

Set objXML = New MSXML2.DOMDocument

XMLRequest.Open "GET", "someURL", False
XMLRequest.send

While XMLRequest.Status <> 200
    DoEvents
Wend

If Not objXML.LoadXML(XMLRequest.responseText) Then
    Err.Raise objXML.parseError.ErrorCode, , objXML.parseError.reason
End If

End Sub

我使用的是Microsoft XML V6.0参考资料。

XML响应相当大，但它的格式如下：

<DataSet>
<xs:schema id="NewDataSet">
<xs:element name="NewDataSet" msdata:IsDataSet="true" msdata:UseCurrentLocale="true">
<xs:complexType>
<xs:choice minOccurs="0" maxOccurs="unbounded">
<xs:element name="CANSIM">
<xs:complexType>
<xs:sequence>
<xs:element name="ID" type="xs:int" minOccurs="0"/>
<xs:element name="Date" type="xs:dateTime" minOccurs="0"/>
<xs:element name="Year" type="xs:int" minOccurs="0"/>
<xs:element name="Month" type="xs:int" minOccurs="0"/>
<xs:element name="BondYield_LongTerm_Gov" type="xs:double" minOccurs="0"/>
<xs:element name="BondYield_LongTerm_Prov" type="xs:double" minOccurs="0"/>
<xs:element name="BondYield_LongTerm_Corp" type="xs:double" minOccurs="0"/>
<xs:element name="B14070" type="xs:double" minOccurs="0"/>
<xs:element name="B14072" type="xs:double" minOccurs="0"/>
<xs:element name="B14081" type="xs:double" minOccurs="0"/>
<xs:element name="B14045" type="xs:double" minOccurs="0"/>
<xs:element name="Rolling12MonthAvg" type="xs:double" minOccurs="0"/>
<xs:element name="B14055" type="xs:double" minOccurs="0"/>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:choice>
</xs:complexType>
</xs:element>
</xs:schema>
<diffgr:diffgram>
<NewDataSet>
<CANSIM diffgr:id="CANSIM1" msdata:rowOrder="0">
<ID>462</ID>
<Date>2006-06-01T00:00:00-04:00</Date>
<Year>2006</Year>
<Month>6</Month>
<BondYield_LongTerm_Gov>0.0469</BondYield_LongTerm_Gov>
<BondYield_LongTerm_Prov>0.0518</BondYield_LongTerm_Prov>
<BondYield_LongTerm_Corp>0.0581</BondYield_LongTerm_Corp>
<B14070>0.0458</B14070>
<B14072>0.0467</B14072>
<B14081>0.019</B14081>
<B14045>0.0318</B14045>
<Rolling12MonthAvg>0.026425</Rolling12MonthAvg>
<B14055>0.0328</B14055>
</CANSIM>
<CANSIM diffgr:id="CANSIM2" msdata:rowOrder="1">
<ID>463</ID><Date>2006-07-01T00:00:00-04:00</Date><Year>2006</Year><Month>7</Month>
<BondYield_LongTerm_Gov>0.0446</BondYield_LongTerm_Gov>  
<BondYield_LongTerm_Prov>0.0496</BondYield_LongTerm_Prov>
<BondYield_LongTerm_Corp>0.056</BondYield_LongTerm_Corp>
<B14070>0.0431</B14070>
<B14072>0.0445</B14072>
<B14081>0.018</B14081>
<B14045>0.0318</B14045>
<Rolling12MonthAvg>0.027216666667</Rolling12MonthAvg>
<B14055>0.0328</B14055>
</CANSIM>
</NewDataSet>
</diffgr:diffgram>
</DataSet>

我想获取最近60个月的"B14045“利率和过去5年中第12个月的"Rolling12MonthAvg”。

这些值都是有序的，所以我想我应该能够通过使用" month“和"Year”属性“找到”或“转到”起始月份(60个月前)，然后在递增循环中读取B14045率。

但是我不知道查找节点的语法(？)我想，然后递增前进。我试着阅读了一些帮助文件和其他主题，但我似乎搞不懂。任何帮助都将不胜感激。

xml

vba

excel

回答 1

Stack Overflow用户

发布于 2013-12-20 17:37:05

我想我已经通过使用：http://www.w3schools.com/xpath/xpath_examples.asp弄清楚了XML语法

Sub GetCANSIM()
Dim XMLRequest As New XMLHTTP
Dim objXML As MSXML2.DOMDocument
Dim objNodeList As IXMLDOMNodeList
Dim objNode As IXMLDOMNode
Dim objElement As IXMLDOMElement
Dim strPath As String
Dim lngYear As Long

Set objXML = New MSXML2.DOMDocument

XMLRequest.Open "GET", "someURL",False
XMLRequest.send

While XMLRequest.Status <> 200
    DoEvents
Wend

If Not objXML.LoadXML(XMLRequest.responseText) Then
    Err.Raise objXML.parseError.ErrorCode, , objXML.parseError.reason
End If

objXML.setProperty "SelectionLanguage", "XPath"

lngYear = year(Now()) - 8

'Select all the B14045 elements of the CANSIM elements which are within 5 years
strPath = "//CANSIM[Year>" & lngYear & "]/B14045"

Set objNodeList = objXML.DocumentElement.SelectNodes(strPath)

End Sub