excel - Excel VBA - XML DomDocument 返回属性值
问题描述
我正在使用 Excel VBA 中的以下 XML 响应。
<XXXXX docType="GetSegmentSpeed" copyright="Copyright XXXXX Inc." versionNumber="12.9" createdDate="2018-11-26T15:08:37Z" statusId="0" statusText="" responseId="06d3aad3-c3aa-40a5-9d2c-f1ac8f713729">
<SegmentSpeedResultSet coverage="255">
<SegmentSpeedResults timestamp="2018-11-26T15:08:36Z">
<Segment code="213423027" type="XDS" speed="53" average="34" reference="40" score="30" c-value="63" travelTimeMinutes="0.649" speedBucket="3"/>
<Segment code="213423023" type="XDS" speed="53" average="38" reference="41" score="30" c-value="58" travelTimeMinutes="0.603" speedBucket="3"/>
<Segment code="213423026" type="XDS" speed="52" average="34" reference="39" score="30" c-value="71" travelTimeMinutes="0.486" speedBucket="3"/>
<Segment code="213423050" type="XDS" speed="52" average="34" reference="39" score="30" c-value="71" travelTimeMinutes="0.48" speedBucket="3"/>
<Segment code="213423051" type="XDS" speed="52" average="35" reference="39" score="30" c-value="78" travelTimeMinutes="0.486" speedBucket="3"/>
</SegmentSpeedResults>
</SegmentSpeedResultSet>
</XXXXX>
我想找到Segments的travelTimeMinutes属性的总数。
首先,我想我会尝试获得第一段的价值。这是我的代码:
Sub SegSetTimes()
' Declare Private Variables
Dim SegString As String 'Segment set to be used for calculation
Dim hReq As New WinHttpRequest 'HttpRequest path
Dim strResp As String 'Response String
Dim xmlDoc As MSXML2.DOMDocument60 'DomDocument for parsing XML
' Import Segment Set
SegString = Join(WorksheetFunction.Transpose(Range("A2", Range("A2").End(xlDown)).Value), "|XDS,")
' Call for real-time segment information
hReq.Open "Get", "http://eu.api.XXXXX.com/Traffic/XXXXX.ashx?Action=GetSegmentSpeed" & "&token=" & AuthToken & "&Segments=" & SegString
hReq.Send
' Create string from response text
strResp = hReq.ResponseText
' Import response text into DomDocument for parsing within VBA
Set xmlDoc = New MSXML2.DOMDocument60
If Not xmlDoc.LoadXML(strResp) Then
MsgBox "Load Error"
End If
Dim n As IXMLDOMNodeList
Set n = xmlDoc.SelectNodes("//XXXXX/SegmentSpeedResultSet/SegmentSpeedResults")
Dim TT As Single
TT = n.Item(0).Attributes.getNamedItem("travelTimeMinutes")
End Sub
它失败并出现以下错误:
运行时错误'91':对象变量或未设置块变量'
在Locals中单步执行时,我的IXMLDOMNodeList
n看起来是正确的。我只是看不到如何获得我想要的价值观。
有人有什么建议吗?
解决方案
从文件中读取我使用 XPath 获取相关节点,然后使用 getAttribute 提取值
Public Sub testing()
Dim xmlDoc As New MSXML2.DOMDocument60, items As Object, item As IXMLDOMElement, total As Double
Set xmlDoc = New MSXML2.DOMDocument60
xmlDoc.Load "C:\Users\User\Desktop\Test.xml"
Set items = xmlDoc.SelectNodes("//Segment[@travelTimeMinutes]")
For Each item In items
total = total + item.getAttribute("travelTimeMinutes")
Next
Debug.Print total
End Sub
推荐阅读
- sql - 最小分区 by with Case When
- c++ - GDB 中的“读取变量错误:无法访问地址处的内存”
- xml - 如何在 Xamarin 中的 .xml 文件中包含 .xaml 脚本?
- windows - 为什么 Windows 随机化 PEB 和 TEB?
- google-apps-script - 使用谷歌脚本发送电子邮件
- c++ - C++ 部分概念 id:显式模板规范顺序/第一个参数的特殊状态的原因是什么?
- javascript - 如何从 DOM 元素和选择器的混合中创建 JQuery 容器
- python - Django 休息框架视图集 get_queryset() 不返回正确的查询集
- javascript - 从 localStorage 加载 var n 并循环 n 次
- css - 使父元素“调整大小”以等于最大子元素的宽度(响应式开关切换)