Encontrei este script vba fantástico que pega um endereço e retorna a latitude e longitude usando a API de mapas do Google. No entanto, o Google possui recursos de limitação de taxa e quero fazer isso para um grande número de endereços (aproximadamente 3 mil). O script, por outro lado, não possui limitação de taxa, o que os torna bastante incompatíveis. É possível editar este script para que ele não passe para a próxima célula até que a solicitação anterior seja concluída?
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
Qualquer ajuda seria muito apreciada, vocês são os melhores!
Responder1
O código que você postou simplesmente informa ao Excel como consultar o Google Maps, mas na verdade não faz isso - você precisa chamar a MyGeocode
função e informar ao Excel quais endereços deseja consultar.
Primeiro pressione Alt+ F11para acessar o Editor Visual Basic (VBE). Clique com o botão direito no nome da sua pasta de trabalho no painel esquerdo e selecioneInserir>Módulo. Cole o VBA neste novo módulo.
Para executar este código você precisa adicionar uma referência aoXML da Microsoft, v6.0biblioteca. No VBE cliqueFerramentas>Referências, role para baixo e marque a caixa ao lado da biblioteca. Clique OK.
Agora, de volta ao próprio Excel.
Digamos que você tenha um subconjunto de 3 milhões de endereços nas colunas A:D.
Na coluna E concatene todos os seus campos de endereço para obter o endereço completo em uma célula:
=A2&" "&B2&" "&C2&" "&D2
Então, na coluna F, podemos chamar as coordenadas do Google da seguinte forma:
=MyGeocode(E2)
Não há necessidade de dizer ao Excel para fazer uma pausa entre as chamadas - você controla isso pelo número de vezes que diz ao Excel para executar =MyGeocode()
- ou seja, quantas linhas no Excel você copia a fórmula.
Por segurança, execute isso em no máximo 2.500 linhas por dia para cumprirTermos e Condições do Google(obrigado Seth), a menos que você esteja disposto a gastar algum dinheiro para aumentar o número de ligações que pode fazer para 100.000 por dia.
Responder2
Você pode fazer o VBA interromper um programa por, digamos, 5 segundos da seguinte maneira:
Application.Wait(Now + TimeValue("0:00:05"))
O que pode funcionar para você
Responder3
Se você tiver três milhões de endereços, considere obter um acesso comercial que permita usar o serviço sem limitação de taxa. Caso contrário, use algo como:
Msgbox "Click to go on", , "Example"
A partir doTermos de uso:
d. Limitações da API
O Google define e impõe limites ao uso das APIs (por exemplo, limitando o número de solicitações de API que você pode fazer ou o número de usuários que você pode atender), a nosso exclusivo critério. Você concorda e não tentará contornar tais limitações documentadas em cada API. Se desejar usar qualquer API além desses limites, você deverá obter o consentimento expresso do Google (e o Google poderá recusar tal solicitação ou condicionar a aceitação de sua concordância com termos e/ou cobranças adicionais para esse uso). Para obter tal aprovação, entre em contato com a equipe relevante da API do Google para obter informações (por exemplo, usando o console de desenvolvedores do Google).