我發現了這個很棒的 vba 腳本,它使用 google 地圖 API 獲取地址並返回緯度和經度。但是,google 有速率限制功能,我想對大量位址(大約 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 貼到這個新模組中。
要運行此程式碼,您需要添加對微軟 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
如果您有 300 萬個地址,您可能需要考慮取得商業存取權限,以便您在沒有速率限制的情況下使用該服務。否則使用類似的東西:
Msgbox "Click to go on", , "Example"
正如從使用條款:
d. API 限制
Google 可自行決定對您對 API 的使用設定並實施限制(例如限制您可以發出的 API 請求數量或您可以服務的使用者數量)。您同意且不會試圖規避每個 API 中記錄的此類限制。如果您想使用超出這些限制的任何 API,則必須獲得 Google 的明確同意(並且 Google 可以拒絕此類請求或在您同意該使用的附加條款和/或費用的情況下接受此類請求)。若要尋求此類批准,請聯絡相關的 Google API 團隊以取得資訊(例如,透過使用 Google 開發人員控制台)。