Excel VBA - XML DomDocument return attribute values

Question

I am working with the following XML response in Excel VBA.

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

I want to find the total of the travelTimeMinutes attributes of Segments.

To begin with, I thought I would try and get the value for the first segment. This is my code:

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

It fails with the following error:

Run-time error '91': Object variable or With block variable not set'

When stepping through in Locals, my IXMLDOMNodeList n looks correct. I just cannot see how to get at the values I want to.

Does anybody have any suggestions?

Parfait · Accepted Answer · 2018-11-26 17:08:51Z

1

Reading in from a file I use an XPath to get the relevant nodes and then extract the value using 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

edited Nov 26, 2018 at 17:08

Parfait

108k19 gold badges103 silver badges138 bronze badges

answered Nov 26, 2018 at 16:32

QHarr

84.5k14 gold badges58 silver badges105 bronze badges

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

Comments

Parfait · Accepted Answer · 2018-11-26 17:34:23Z

Alternatively, consider running XSLT to retrieve the sum() across all nodes without looping:

XSLT (save as .xsl file, a special .xml file to be referenced in VBA)

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
  <xsl:output indent="yes"/>
  <xsl:strip-space elements="*"/>

  <xsl:template match="/XXXXX">
    <result>
      <xsl:value-of select="format-number(sum(descendant::Segment/@travelTimeMinutes), '#.###')"/>
    </result>
  </xsl:template>

</xsl:stylesheet>

XSLT Demo

VBA

Dim xmlDoc As New MSXML2.DOMDocument60, items As Object, item As IXMLDOMElement, total As Double
' NEW REFERENCES    
Dim xslDoc As New MSXML2.DOMDocument60, newDoc As New MSXML2.DOMDocument60

' RETRIEVE WEB REQUEST   
...same code as above...

' LOAD XML AND XSL FILES 
xmlDoc.async = False
xml.LoadXML strResp

xslDoc.async = False
xslDoc.Load "C:\Path\to\XSLT\File.xsl"

' TRANSFORM XML
xmlDoc.transformNodeToObject xslDoc, newDoc

' OUTPUT RESULT (NO LOOPING)
Debug.Print newDoc.SelectSingleNode("/result").Text
' 2.704

Collectives™ on Stack Overflow

Excel VBA - XML DomDocument return attribute values

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related