jueves, 10 de septiembre de 2015

VBA: Latitud y Longitud de una dirección en Excel.

Hace unas semanas, buscando información sobre coordenadas geográficas de diferentes puntos, me encontré con una función personalizada en VBA (UDF) en la web www.policeanalyst.com que me pareció muy útil...
Por ese motivo me permito subirlo en el blog... con unos mínimos ajustes.

Se trata de una función que recupera los datos de Longitud y Latitud de cualquier dirección desde la herramienta de Google Maps, en su forma decimal.
Lo interesante de este dato es que a partir de la información recuperada, podremos plasmar diferentes datos sobre mapas dentro de nuestro Excel.. empleando herramientas como Power View, Mapas de Bing, etc...


Insertamos el código de la función en un módulo estándar de nuestro proyecto de VBA desde el editor de VB; sin olvidar de agregar la Referencia: Microsoft XML v6.0

Function GoogleGeoCode(address As String, coordenada As String) As Double
'Agregar la Referencia: Microsoft XML v6.0
Dim strAddress As String
Dim strQuery As String
Dim strLatitude As String
Dim strLongitude As String
'Nota: el parámeto 'coordenada' será el texto: 'Latitud' o 'Longitud'

'la función URLEncode transforma la dirección buscada
'en un literal para la búsqueda en Google...
strAddress = URLEncode(address)

'Montamos la cadena de texto par la consulta en el mapa de Google Maps
strQuery = "http://maps.googleapis.com/maps/api/geocode/xml?"
strQuery = strQuery & "address=" & strAddress
strQuery = strQuery & "&sensor=false"

'definimos los componentes XML and HTTP a emplear
Dim googleResult As New MSXML2.DOMDocument
Dim googleService As New MSXML2.XMLHTTP
Dim oNodes As MSXML2.IXMLDOMNodeList
Dim oNode As MSXML2.IXMLDOMNode

'Generamos la consulta en HTTP en la URL de Google...
'asegunrándonos del False para una operación síncrona
googleService.Open "GET", strQuery, False
googleService.send
googleResult.LoadXML (googleService.responseText)

Set oNodes = googleResult.getElementsByTagName("geometry")

'condicionamos la variable 'geometry' obtenida para recuperar
'nuestros parámetros: latitud y longitud
If oNodes.Length = 1 Then
    For Each oNode In oNodes
      strLatitude = oNode.ChildNodes(0).ChildNodes(0).Text
      strLongitude = oNode.ChildNodes(0).ChildNodes(1).Text
      
      If coordenada = "Latitud" Then
        GoogleGeoCode = (Val(Replace(strLatitude, ",", ".")))
      ElseIf coordenada = "Longitud" Then
        GoogleGeoCode = (Val(Replace(strLongitude, ",", ".")))
      End If
    Next oNode
Else
    GoogleGeoCode = Val(0)
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



El resultado lo podemos ver en la imagen siguiente:


En el campo Latitud de nuestra Tabla hemos insertado la función:
=GoogleGeocode([@Estado];"Latitud")
y en el campo Longitud:
=GoogleGeocode([@Estado];"Longitud")

14 comentarios:

  1. Este comentario ha sido eliminado por el autor.

    ResponderEliminar
  2. Este comentario ha sido eliminado por el autor.

    ResponderEliminar
  3. La verdad muy interesante intente hacerlo pero no me esta resultado
    Me indica un error de compilacion
    No se ha definido el tipo definido por el usuario y me marca
    "googleResult As New MSXML2.DOMDocument"

    ResponderEliminar
    Respuestas
    1. debe ser por la version 2003. no me andaba en esa. y si anduvo en la 2010

      Eliminar
  4. Yo la probé en mi versión que es la 2013

    ResponderEliminar
    Respuestas
    1. Hola José Emilio,
      es muy importante habilitar la referencia Microsoft XML v6.0... que lógicamente depende de la versión de Excel que tengamos...
      Saludos

      Eliminar
  5. Bien interesante esta entrada, podría tener variadas utilidades, como por ejemplo calcular distancias entre localidades.

    Quisiera saber entonces, como la dirección viene dada por el código postal (cp) quisiera saber si existe algo asi como un ANSI standart donde estén todos los CP´s del mundo y si se puede hacer una UDF similar que al darle el municipio y país me devuelva dicho código. Gracias y saludos.

    ResponderEliminar
    Respuestas
    1. Hola,
      no solo por el CP, valdría el nombre de la ciudad (pero siempre es mejor ajustar y ser preciso).
      Realmente desconozco si existe un ANSI con los CP del mundo, pero parece complicado, ya que cada municipio puede tener muchos CP, y hace complicada la relación 1-a-1 por la que preguntas...
      Slds

      Eliminar
  6. ... con relación a mi anterior comentario, cómo saber los formatos de entrada para obtener datos de google map y que otra información puede suministrar.

    Existen infinidad de entidades internacionales que manejan datos y estadísticas globales, tales como UNICEF, ONU, NASA y otras que manejan códigos standares globales (creo que ANSI), todas ellas con acceso web.

    Cómo aprovechar este ejemplo para obtener datos de estas web? ... tienes un modelo genérico...

    Supón este caso: quiero que diariamente en una hoja excel me diga el valor del €uro, el Dólar y la Libra esterliana de acuerdo a la fecha del día, sin tener que buscarlos y escribirlos. Gracias de nuevo y más saludos.

    ResponderEliminar
  7. Tengo una pregunta; al correr el codigo en la linea 34 que dice:
    If oNodes.Length = 1 Then
    me da un valor de 2 y no 1 y por consiguiente no ofrece el resultado; esto tiene alguna significancia especial? digo, porque si le pongo = 2 si se jecuta y dá el resultado

    ResponderEliminar
    Respuestas
    1. Hola,
      mide el número de caracteres de la etiqueta 'geometry' en la web/url...
      podría ser que con el tiempo haya cambiado y ahora tenga dos caracteres en lugar de 1

      Saludos

      Eliminar
  8. en vba como detecto las coordenadas es decir abscisa y ordenada gracias

    ResponderEliminar
    Respuestas
    1. Hola,
      qué tal estás?, un placer saludarte igualmente.

      Si te refieres a coordenadas de un mapa puedes emplear lo descrito en este post; si te refieres a coordenadas de un gráfico solo tienes que recuperar los valores de los ejes...
      No creo haga falta VBA para esto.

      Saludos cordiales

      Eliminar

Nota: solo los miembros de este blog pueden publicar comentarios.