How to access XML response using Excel VBA

Question

I have an excel spreadsheet where I have 25,000+ records with Lat/Lon coordinates and other data. I am trying to use an Excel VBA script to look up an associated County name based on the Lat/Lon using the following US Census web service link (an example coordinate included).

https://geo.fcc.gov/api/census/block/find?latitude=40.000&longitude=-90.000&format=xml

this returns the following response xml.

<Response status="OK" executionTime="0">
    <Block FIPS="170179601002012" bbox="-90.013605,39.996144,-89.994837,40.010663"/>
    <County FIPS="17017" name="Cass"/>
    <State FIPS="17" code="IL" name="Illinois"/>
</Response>

The problem I have is that I need to access the "name" value (i.e.,'Cass', in this case) contained in County node, and this value will be copied into the Excel spreadsheet under the County column. Is there a way to access this value? The XML response is not in the standard form I would expect (I'm new to XML), <County>Cass</County> so I'm unsure how I would access the value I need from this returned response.

The whole XML connection and response part of the script seem to be working fine, I just need to know how get the values from the response for the node in question.

Here is what I have so far. Any help would be greatly appreciated. If you need the full code, let me know.

standard XML connection stuff here...

XmlResponse = oXMLHTTP.responseText

'Process the XML to get County name

strXML = XmlResponse
Set XDoc = New MSXML2.DOMDocument60

If Not XDoc.LoadXML(XmlResponse) Then
    Err.Raise XDoc.parseError.ErrorCode, , XDoc.parseError.reason
End If

Set xNode = XDoc.SelectSingleNode("/Response/County")
MsgBox xNode.Text

'Insert County name into Excel
Cells(i + 2, 14).Value = xNode.Text

I am assuming that the xNode.Text part is where I need help in selecting the right part from the response (?).

Many thanks!

T.M. · Accepted Answer · 2020-05-13 17:26:07Z

An alternative via WorksheetFunction `FilterXML()

If you dispose of Excel vers. 2013+ you can execute the following:

Sub ExampleCall()
Dim myXML As String, myXPath As String
myXML = WorksheetFunction.WebService("https://geo.fcc.gov/api/census/block/find?latitude=40.000&longitude=-90.000&format=xml")

myXPath = "//County/@name"               

Debug.Print WorksheetFunction.FilterXML(myXML, myXPath)   ' ~> Cass
End Sub

Further hints to FilterXML() and its XPath argument

Starting by a double slash // the XPath string "//County/@name" searches

the <County> node at any hierarchy level returning
the subordinal @name attribute which has to be identified by a leading @. The FilterXML() function returns its textual content.

See FilterXML() function and WebService() function.

Of course it's possible to use both functions directly in worksheet formulae.

user2121627 · Accepted Answer · 2020-05-13 13:53:13Z

1

In searching around some more today I found a solution to my original question.

For those interested, you can access the County attribute 'name' in the returned xml response and write it out by replacing the above portion of code with the following:

Original:
Set xNode = XDoc.SelectSingleNode("/Response/County")
MsgBox xNode.Text

Updated:
Set xNode = XDoc.SelectSingleNode("//Response/County/@name")
MsgBox xNode.Text

answered May 13, 2020 at 13:53

user2121627

417 bronze badges

1 Comment

T.M. Over a year ago

Posted an alternative using the FilterXML() function with further explanations to XPath and the leading double slashes you could shorten to "//County/@name" btw. - Further hint: try to use fully qualified range references, e.g. via a worksheet's Code(Name) Sheet1.Cells(i + 2, 14).Value = xNode.Text as otherwise VBA takes values of any sheet currently active.

Collectives™ on Stack Overflow

How to access XML response using Excel VBA

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related