XML转excel -反向地理编码

Question

我正在使用mapzen对MS Excel中的坐标列表(在前2列中)进行反向地理编码。输出应该是从mapzen返回的字符串，然后粘贴到第三列中。Mapzen以geoJSON格式返回结果。我引用了XML v6。

Mapzen api return

然后，我将使用Excel中的搜索来匹配地址，这样返回的长字符串就不需要解析了。到目前为止，代码编译了http字符串。我没有任何XML方面的经验，但这是我到目前为止的尝试。

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

最终的输出是这样的。

Desired Output

任何帮助都将不胜感激！

Answer 1

所以我在youtube频道上找到了一段关于json vba交互的视频，叫做red stanpler。这完全符合我的需求。

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