我发现了一个很棒的 vba 脚本,它使用谷歌地图 API 获取地址并返回经纬度。但是,谷歌具有速率限制功能,我想对大量地址(约 300 万)执行此操作。另一方面,该脚本没有速率限制,这使得它们相当不兼容。是否可以编辑此脚本,使其在完成上一个请求之前不会移动到下一个单元格?
Function MyGeocode(address As String) As String
Dim strAddress As String
Dim strQuery As String
Dim strLatitude As String
Dim strLongitude As String
strAddress = URLEncode(address)
'Assemble the query string
strQuery = "http://maps.googleapis.com/maps/api/geocode/xml?"
strQuery = strQuery & "address=" & strAddress
strQuery = strQuery & "&sensor=false"
'define XML and HTTP components
Dim googleResult As New MSXML2.DOMDocument60
Dim googleService As New MSXML2.XMLHTTP60
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
googleService.Open "GET", strQuery, False
googleService.send
googleResult.LoadXML (googleService.responseText)
Set oNodes = googleResult.getElementsByTagName("geometry")
If oNodes.Length = 1 Then
For Each oNode In oNodes
strLatitude = oNode.ChildNodes(0).ChildNodes(0).Text
strLongitude = oNode.ChildNodes(0).ChildNodes(1).Text
MyGeocode = strLatitude & "," & strLongitude
Next oNode
Else
MyGeocode = "Not Found (try again, you may have done too many too fast)"
End If
End Function
Public Function URLEncode(StringVal As String, Optional SpaceAsPlus As Boolean = False) As String
Dim StringLen As Long: StringLen = Len(StringVal)
If StringLen > 0 Then
ReDim result(StringLen) As String
Dim i As Long, CharCode As Integer
Dim Char As String, Space As String
If SpaceAsPlus Then Space = "+" Else Space = "%20"
For i = 1 To StringLen
Char = Mid$(StringVal, i, 1)
CharCode = Asc(Char)
Select Case CharCode
Case 97 To 122, 65 To 90, 48 To 57, 45, 46, 95, 126
result(i) = Char
Case 32
result(i) = Space
Case 0 To 15
result(i) = "%0" & Hex(CharCode)
Case Else
result(i) = "%" & Hex(CharCode)
End Select
Next i
URLEncode = Join(result, "")
End If
End Function
任何帮助都将不胜感激,你们是最棒的!
答案1
您发布的代码只是告诉 Excel 如何查询 Google 地图,它实际上并没有执行此操作 - 您需要调用该MyGeocode
函数并告诉 Excel 您要查询哪些地址。
首先按Alt+F11访问 Visual Basic 编辑器 (VBE)。右键单击左侧面板中工作簿的名称,然后选择插入>模块. 将 VBA 粘贴到这个新模块中。
要运行此代码,您需要添加对Microsoft XML,v6.0库。在 VBE 中单击工具>参考,向下滚动并勾选库旁边的框。点击确定。
现在回到 Excel 本身。
假设您在 A:D 列中有一个 300 万个地址的子集。
在 E 列中连接所有地址字段,以便在一个单元格中获得整个地址:
=A2&" "&B2&" "&C2&" "&D2
然后在 F 列中我们可以从 Google 调用坐标,如下所示:
=MyGeocode(E2)
无需告诉 Excel 在调用之间暂停 - 您可以通过告诉 Excel 运行的次数来控制这一点=MyGeocode()
- 即,您在 Excel 中复制了多少行公式。
为了安全起见,每天运行不超过 2,500 行,以符合Google 的条款和条件(感谢 Seth),除非您愿意花一些钱将您每天可以拨打的电话数量增加到 100,000 个。
答案2
您可以让 VBA 暂停某个程序,比如说,5 秒钟,如下所示:
Application.Wait(Now + TimeValue("0:00:05"))
哪个可能对你有用
答案3
如果您有三百万个地址,您可能需要考虑获取商业访问权限,以便您不受速率限制地使用该服务。否则请使用类似以下内容:
Msgbox "Click to go on", , "Example"
从使用条款:
d.API 限制
Google 可自行决定设置并强制执行您对 API 的使用限制(例如,限制您可以发出的 API 请求数量或您可以服务的用户数量)。您同意并且不会试图规避每个 API 中记录的此类限制。如果您想使用超出这些限制的任何 API,您必须获得 Google 的明确同意(Google 可能会拒绝此类请求或以您同意附加条款和/或针对该使用收取费用为条件)。要获得此类批准,请联系相关的 Google API 团队获取信息(例如,通过使用 Google 开发者控制台)。