如何使用VBA和Google API密钥检索地址全名? [英] How to retrieve address full name by using VBA and Google API key?

问题描述

我正在努力使用当前使用的VBA代码检索完整的地址名称，

I'm struggling with retrieving a full address name with the VBA code I'm currently using,

我认为我的问题出在street_number部分，但是当我将其更改为long_name时，则不会生成任何结果.

I think my issue lies in the street_number section but when I change it to long_name no results gets generated.

我是VBA的新手，有关此问题的任何帮助将不胜感激.谢谢

I'm new to VBA, any help with this issue will be appreciated. Thank you

Sub myTest()
Dim xhrRequest As XMLHTTP60
Dim domDoc As DOMDocument60
Dim domDoc2 As DOMDocument60
Dim placeID As String
Dim query As String
Dim nodes As IXMLDOMNodeList
Dim node As IXMLDOMNode
Dim rng As Range, cell As Range
Set rng = Range("B1")

For Each cell In rng

'you have to replace spaces with +
query = cell.Value

'You must acquire a google api key and enter it here
Dim googleKey As String
googleKey = "Your API key" 'your api key here

'Send a "GET" request for place/textsearch
Set xhrRequest = New XMLHTTP60

xhrRequest.Open "GET", "https://maps.googleapis.com/maps/api/place/textsearch/xml?" & _
    "query=" & query & "&key=" & googleKey, False
xhrRequest.send

'Save the response into a document
Set domDoc = New DOMDocument60
domDoc.LoadXML xhrRequest.responseText

'Find the first node that is called "place_id" and is the child of the "result" node
placeID = domDoc.SelectSingleNode("//result/place_id").Text

'recycling objects (could just use new ones)
Set domDoc = Nothing
Set xhrRequest = Nothing

'Send a "GET" request for place/details
Set xhrRequest = New XMLHTTP60
xhrRequest.Open "GET", "https://maps.googleapis.com/maps/api/place/details/xml?placeid=" & placeID & _
"&key=" & googleKey, False
xhrRequest.send

'Save the response into a document
Set domDoc = New DOMDocument60
domDoc.LoadXML xhrRequest.responseText

Dim output As String
Dim s As String

'hacky way to get postal code, you might want to rewrite this after learning more
Set nodes = domDoc.SelectNodes("//result/address_component/type")
For Each node In nodes
    s = node.Text
    If s = "street_number" Then
        'this is bad, you should search for "long_name", what i did here was assume that "long_name was the first child"
        'output = vbNewLine & "Postal Code: " & node.ParentNode.FirstChild.Text
        cell.Offset(0, 1).Value = "Address: " & node.ParentNode.FirstChild.Text
    End If

    If s = "postal_code" Then
        'this is bad, you should search for "long_name", what i did here was assume that "long_name was the first child"
        'output = vbNewLine & "Postal Code: " & node.ParentNode.FirstChild.Text
        cell.Offset(0, 2).Value = "Postal Code: " & node.ParentNode.FirstChild.Text
    End If
Next node

Next cell
'output
'MsgBox "Formatted Address: " & domDoc.SelectSingleNode("//result/formatted_address").Text & output
End Sub

我得到的回报:

我需要的是:

推荐答案

也许只是将整个地址作为一个块进行检索:

Maybe just retrieve the entire address as a single block:

Set nodes = domDoc.SelectNodes("//result/*")
For Each node In nodes
    s = node.Text
    If node.nodeName = "formatted_address" Then
        cell.Offset(0, 1).Value = "Address: " & s
    End If
Next node

这篇关于如何使用VBA和Google API密钥检索地址全名?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！