如何使用 Excel VBA 搜索街道地址并将值返回到 Excel 中？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24460488/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to Google the street address and return the value back into Excel using Excel VBA?
提问by user3784616
I have a list of over 50,000 places in Excel (eg. Arches National Park, Cornell Museum of Glass). When I type "Cornell Museum of Glass" into Google, it returns an address of "1 Museum Way, Corning, NY 14830". I need the full street address Street #, Street name, and postal code. I do have the state NY though(in Excel). Does anyone know how to search the address on Google(typing Cornell Museum of Glass), then return the street address back onto Excel(1 Museum Way, Corning, NY 14830)? I know VBA is capable of doing this, and I don't want to manually do it 50,000 times, plus I'm a total newbie at VBA. If anyone knows how I would greatly appreciate it! Thank you in advance.
我在 Excel 中有超过 50,000 个地点的列表（例如拱门国家公园、康奈尔玻璃博物馆）。当我在 Google 中输入“Cornell Museum of Glass”时，它会返回地址“1 Museum Way, Corning, NY 14830”。我需要完整的街道地址Street #、街道名称和邮政编码。我确实有纽约州（在 Excel 中）。有谁知道如何在谷歌上搜索地址（输入康奈尔玻璃博物馆），然后将街道地址返回到 Excel（1 Museum Way, Corning, NY 14830）？我知道 VBA 能够做到这一点，而且我不想手动执行 50,000 次，而且我是 VBA 的新手。如果有人知道我将不胜感激！先感谢您。
回答by Alter
The following should be the information needed to solve the problem
以下应该是解决问题所需要的信息
Hereis a post that shows how to use VBA and XML to interact with Google Maps (via the google maps api).
And Hereis google's Place API, which is what you're looking for (requesting/receiving information from google).
这是一篇文章，展示了如何使用 VBA 和 XML 与谷歌地图交互（通过谷歌地图 api）。
而这是谷歌的地方API，这是你在找什么（请求/从谷歌接收信息）。
Answer
I have created a working test. You have to create a google API key, you can go hereto do that. Once you have the API key, put it into the code and run it.
答案
我创建了一个工作的考验。你必须创建一个谷歌 API 密钥，你可以去这里做。获得 API 密钥后，将其放入代码并运行。
Note that Google does restrict how often an API key can be used each day. A free user seems to get 1000 "requests" to the "place" API each day. Annoyingly, some calls seem to be worth more "requests" than others. My code makes 2 calls to google's "place" API, but google counts them as 11 requests. This is because a call to "place/textsearch" is worth 10 "requests". My other call was to places/details is worth 1. All this means is that you're limited to 90 entries each day/free api key with this method.
请注意，Google 确实限制了每天使用 API 密钥的频率。免费用户似乎每天都会收到 1000 个“请求”到“地点”API。令人讨厌的是，有些电话似乎比其他电话更值得“请求”。我的代码对谷歌的“地方”API 进行了 2 次调用，但谷歌将它们计为 11 个请求。这是因为对“place/textsearch”的调用值 10 个“请求”。我的另一个电话是地点/详细信息价值 1。所有这一切意味着您每天只能使用 90 个条目/使用此方法获得免费 api 密钥。
Hereis Google's place/search documentation
Hereis Google's place/details documentation
这是 Google 的位置/搜索文档
这是 Google 的位置/详细信息文档
Finally, thiswas a terrific post for this topic, I based my code off it.
最后，这是一篇关于这个主题的了不起的帖子，我的代码基于它。
Test output:
测试输出：
Code:
代码：
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
'you have to replace spaces with +
query = "Cornell+Museum+of+Glass"
'You must acquire a google api key and enter it here
Dim googleKey As String
googleKey = "imaginary 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=Cornell+Museum+of+Glass&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 = "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
End If
Next node
'output
MsgBox "Formatted Address: " & domDoc.SelectSingleNode("//result/formatted_address").Text & output
End Sub
'####
'####
EDIT: Now using place API rather than Geocoding API
编辑：现在使用地点 API 而不是地理编码 API
回答by JonS2017
I had an error similar to a poster above that I fixed and wanted to post the fix somewhere as I am now using a modified version of this code which once it works I will also post. What was happening is I was passing names that google had no results for. If you may be in a similar boat the following line is a positive check for if you got something back: (the negitive is not = "ZERO_RESULTS")
我有一个类似于上面我修复的海报的错误，我想在某处发布修复程序，因为我现在正在使用此代码的修改版本，一旦它起作用，我也会发布。发生的事情是我传递了谷歌没有结果的名字。如果您可能在类似的船上，则以下行是对您是否有回馈的积极检查：（否定不是 =“ZERO_RESULTS”）
if domDoc.selectSingleNode("//status").Text = "OK"
回答by JonS2017
You have to get an API key and then the code will work just fine. Go to the link below and click the button that reads 'GET A KEY'.
您必须获得一个 API 密钥，然后代码才能正常工作。转到下面的链接，然后单击“GET A KEY”按钮。
https://developers.google.com/maps/documentation/javascript/tutorial
https://developers.google.com/maps/documentation/javascript/tutorial
