Encontré este fantástico script vba que toma una dirección y devuelve la latitud y longitud usando la API de mapas de Google. Sin embargo, Google tiene funciones que limitan la velocidad y quiero hacer esto para una gran cantidad de direcciones (aproximadamente 3 millones). El guión, por otro lado, no tiene limitación de velocidad, lo que los hace bastante incompatibles. ¿Es posible editar este script para que no pase a la siguiente celda hasta que se haya completado la solicitud anterior?
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
Cualquier ayuda será muy apreciada, ¡ustedes son los mejores!
Respuesta1
El código que ha publicado simplemente le dice a Excel cómo consultar Google Maps, en realidad no lo hace; debe llamar a la MyGeocode
función y decirle a Excel qué direcciones desea consultar.
Primero presione Alt+ F11para acceder al Editor de Visual Basic (VBE). Haga clic derecho en el nombre de su libro de trabajo en el panel izquierdo y seleccioneInsertar>Módulo. Pegue el VBA en este nuevo módulo.
Para ejecutar este código es necesario agregar una referencia alMicrosoft XML, v6.0biblioteca. En el VBE haga clicHerramientas>Referencias, desplácese hacia abajo y marque la casilla junto a la biblioteca. Haga clic en Aceptar.
Ahora volvamos al propio Excel.
Digamos que tiene un subconjunto de sus 3 millones de direcciones en las columnas A:D.
En la columna E, concatene todos los campos de su dirección para obtener la dirección completa en una celda:
=A2&" "&B2&" "&C2&" "&D2
Luego, en la columna F podemos llamar las coordenadas de Google de la siguiente manera:
=MyGeocode(E2)
No es necesario decirle a Excel que haga una pausa entre llamadas; usted controla esto por la cantidad de veces que le dice a Excel que se ejecute =MyGeocode()
, es decir, cuántas filas en Excel copia la fórmula.
Para estar seguro, ejecute esto en no más de 2500 líneas por día para cumplir conTérminos y condiciones de Google(gracias Seth), a menos que esté dispuesto a gastar algo de dinero para aumentar la cantidad de llamadas que puede realizar a 100.000 por día.
Respuesta2
Puede hacer que VBA detenga un programa durante, digamos, 5 segundos de la siguiente manera:
Application.Wait(Now + TimeValue("0:00:05"))
¿Cuál podría funcionar para ti?
Respuesta3
Si tiene tres millones de direcciones, es posible que desee considerar obtener un acceso comercial que le permita utilizar el servicio sin limitación de tarifas. De lo contrario use algo como:
Msgbox "Click to go on", , "Example"
a partir delCondiciones de uso:
d. Limitaciones de la API
Google establece y aplica límites en su uso de las API (por ejemplo, limitando la cantidad de solicitudes de API que puede realizar o la cantidad de usuarios a los que puede atender), a su entera discreción. Usted acepta y no intentará eludir dichas limitaciones documentadas con cada API. Si desea utilizar cualquier API más allá de estos límites, debe obtener el consentimiento expreso de Google (y Google puede rechazar dicha solicitud o condicionar la aceptación de su aceptación de términos y/o cargos adicionales por ese uso). Para solicitar dicha aprobación, comuníquese con el equipo de API de Google correspondiente para obtener información (por ejemplo, utilizando la consola de desarrolladores de Google).