Я нашел этот фантастический скрипт vba, который берет адрес и возвращает широту и долготу, используя API карт Google. Однако у Google есть функции ограничения скорости, а я хочу сделать это для большого количества адресов (примерно 3 млн). Скрипт, с другой стороны, не имеет ограничения скорости, что делает их довольно несовместимыми. Можно ли отредактировать этот скрипт так, чтобы он не переходил к следующей ячейке, пока не будет выполнен предыдущий запрос?
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 Maps, но на самом деле он этого не делает — вам нужно вызвать функцию MyGeocode
и сообщить Excel, какие адреса вы хотите запросить.
Сначала нажмите Alt+ F11, чтобы открыть редактор Visual Basic (VBE). Щелкните правой кнопкой мыши имя вашей книги на левой панели и выберитеВставлять>Модуль. Вставьте VBA в этот новый модуль.
Для запуска этого кода вам необходимо добавить ссылку наMicrosoft XML, версия 6.0Библиотека. В VBE нажмитеИнструменты>Рекомендации, прокрутите вниз и отметьте галочкой поле рядом с библиотекой. Нажмите OK.
Теперь вернемся к самому Excel.
Допустим, у вас есть подмножество из 3 миллионов адресов в столбцах A:D.
В столбце E объедините все поля адреса, чтобы получить весь адрес в одной ячейке:
=A2&" "&B2&" "&C2&" "&D2
Затем в столбце F мы можем вызвать координаты из Google следующим образом:
=MyGeocode(E2)
Нет необходимости указывать Excel делать паузу между вызовами — вы контролируете это, указывая, сколько раз Excel должен запуститься =MyGeocode()
, т. е. на сколько строк в Excel вы копируете формулу.
Чтобы быть уверенным, запускайте это не более чем на 2500 строках в день, чтобы соответствоватьУсловия и положения Google(спасибо, Сет), если только вы не готовы потратить немного денег, чтобы увеличить количество звонков, которые вы можете сделать, до 100 000 в день.
решение2
Вы можете заставить VBA остановить программу, скажем, на 5 секунд, следующим образом:
Application.Wait(Now + TimeValue("0:00:05"))
Что может подойти вам
решение3
Если у вас три миллиона адресов, вы можете рассмотреть возможность получения коммерческого доступа, который позволит вам использовать сервис без ограничения скорости. В противном случае используйте что-то вроде:
Msgbox "Click to go on", , "Example"
Как изУсловия эксплуатации:
г. Ограничения API
Google устанавливает и применяет ограничения на использование вами API (например, ограничивая количество запросов API, которые вы можете сделать, или количество пользователей, которых вы можете обслуживать) по своему собственному усмотрению. Вы соглашаетесь с такими ограничениями, задокументированными для каждого API, и не будете пытаться их обойти. Если вы хотите использовать какой-либо API за пределами этих ограничений, вы должны получить прямое согласие Google (и Google может отклонить такой запрос или обусловить принятие вашего соглашения дополнительными условиями и/или сборами за такое использование). Чтобы получить такое одобрение, свяжитесь с соответствующей командой Google API для получения информации (например, с помощью консоли разработчиков Google).