XML to excel - Reverse geocoding

Question

I am using mapzen to reverse geocode coordinates for a list of coordinates (in the first 2 columns) in MS Excel. The output should be the returned string from mapzen to be pasted in the third column. Mapzen returns the result in geoJSON. I have referenced XML v6.

Mapzen api return

I will then use search in Excel to match addresses so the long string returned does not need parasing. The code so far compiles the http string. I dont have any experience in XML but here is my attempt so far.

Sub Map()

' Map Macro
'
Public Function geoRevGeocode(lat As String, lng As String) As String
  Dim strQuery As String
  Dim strLatitude As String
  Dim strLongitude As String
  'Dim address As String

  'Assemble the query string
  strQuery = "https://search.mapzen.com/v1/reverse?api_key=search-rK7fDCA&point."
  strQuery = strQuery & "lat=" & lat
  strQuery = strQuery & "&point.lon=" & lng
  strQuery = strQuery & "&username=claudiowyss" 
  'MsgBox (strQuery)

  'define XML and HTTP components

  Dim MapzenResult As Object
  Set MapzenResult = CreateObject("MSXML2.DOMDocument")
  Dim MapzenService As Object
  Set MapzenService = CreateObject("MSXML2.XMLHTTP")

  Dim oNodes As MSXML2.IXMLDOMNodeList
  Dim oNode As MSXML2.IXMLDOMNode

  'create HTTP request to query URL - make sure to have
  'that last "False" there for synchronous operation
  MapzenService.Open "GET", strQuery, False
  MapzenService.send
  MapzenResult.LoadXML (MapzenService.responseText)

  'Need help copying mapzen returned string, to be pasted in the 3rd column in excel sheet


End Function

With the final output to be something like this.

Desired Output

Any help would be much appreciated!

The data format is json not xml. Refer to this http://stackoverflow.com/questions/2782076/is-there-a-json-parser-for-vb6-vba/21664962#21664962 — Jules, Feb 02 '16 at 23:46

score 0 · Answer 1 · answered Feb 03 '16 at 09:00

So I found a video on json vba interaction on the youtube channel called red stanpler. This works perfectly for my needs.

Sub Map()
'
' Map Macro
'
Public Function geoRevGeocode(lat As String, lng As String) As String
  Dim strQuery As String
  Dim strLatitude As String
  Dim strLongitude As String

  strQuery = "https://search.mapzen.com/v1/reverse?api_key=search-rK7fDCA&point."
  strQuery = strQuery & "lat=" & lat
  strQuery = strQuery & "&point.lon=" & lng
  strQuery = strQuery & ".json"

  Set MyRequest = CreateObject("WinHttp.WinHttpRequest.5.1")
  MyRequest.Open "Get", strQuery
  MyRequest.send
  geoRevGeocode = MyRequest.responseText

End Function

XML to excel - Reverse geocoding

1 Answers1