API MATRIZ DE DISTANCIAS EN VBA

Hace tiempo que tengo pendiente escribir algo acerca del cálculo de tiempos y distancias entre varios puntos. Hoy voy a utilizar una función (UDF) muy interesante para obtener estos datos a partir del API de Matriz de Distancias de Maps.

Como bien dice el enunciado vamos a trabajar con matrices a la hora de confeccionar nuestros datos, así que será importante que tengáis claro el concepto y cómo se instrumentaliza en la hoja de trabajo.

Veamos entonces el ejercicio con un caso práctico, imaginémonos que estamos viviendo en Barcelona (disfrutando de un buen paseo por las Ramblas y la zona del puerto) y se nos ocurre que queremos realizar un viaje hasta Coruña (y disfrutar de las vistas desde la Torre de Hércules y del Paseo de los Menhires). Para ello, confeccionaremos un itinerario con las ciudades por las que vamos a pasar. Esta puede ser una buena propuesta (los datos de Coruña están introducidos con coordenadas):

API MATRIZ DE DISTANCIAS_1

Como podéis observar, primero introducimos los dos primeros destinos y luego siempre repetiremos el último para confeccionar correctamente la disposición de los datos. Ahora solo tendremos que construir una función que nos permita obtener el dato de “TIEMPO” y el de “DISTANCIA”.

Para ello vamos a utilizar la siguiente función, que iré comentando:

Function Ruta(Origen, Destino)
'Declaramos las variables a utilizar
Dim URL As String
Dim Row(1 To 1, 1 To 2) As Double
Dim xml, html, Val As Variant
'Aplicamos conversión de caracteres especiales (con la función Caracter_e) al Origen y Destino
Origen = Caracter_e(LCase(Origen))
Destino = Caracter_e(LCase(Destino))
'Componemos la url necesaria para realizar la petición de datos como una matriz
URL = "https://maps.googleapis.com/maps/api/distancematrix/xml?origins=" & Origen & "&destinations=" & Destino _
& "&mode=" ' por defecto la ruta es en coche, el resto de opciones serían:
'"mode=bicycling"- en bicicleta
'"mode=walking"- andando
'Asignamos propiedades a los valores Html y Xml para realizar la llamada
Set html = CreateObject("HTMLFile")
Set xml = CreateObject("Microsoft.XMLHttp")
'Realizamos la petición de información con los datos indicados en la URL y petición sincrónica
xml.Open "POST", URL, False 'en "false" es petición sincrónica
xml.Send
'Capturamos texto de respuesta
html.body.innerHTML = xml.responseText
'Obtenemos los valores para tiempo y distancia
Set Val = html.getElementsByTagName("value")
Row(1, 1) = CLng(Val(0).NextSibling.Data) / 86400 'tiempo (horas)
Row(1, 2) = CLng(Val(1).NextSibling.Data) / 1000 'distancia (km)
'Ya podemos contar con el resultado de los valores al aplicar la función
Ruta = Row
'Liberamos variables
Set xml = Nothing
Set html = Nothing
Erase Row
Val = Empty
End Function

La función Caracter_e, a la que llama la función Ruta, es necesaria para controlar los caracteres extraños, como tildes o letras (ñ), para ello debemos introducir esta función a continuación de la anterior, en el mismo módulo:

Private Function Caracter_e(ByVal especial) As String
Dim Matriz, i, max As Double
'Calculamos el tamaño de la matriz
With Sheets(1)
Final = Application.CountA(.Range("J:J"))
Matriz = .Range(.Cells(2, 9), .Cells(Final, 10))
End With
max = UBound(Matriz)
'Aplicamos un bucle para buscar celdas especiales en el momento de ejecutar la función "Ruta"
For i = 1 To max
especial = Replace(especial, Matriz(i, 1), Matriz(i, 2))
Next
Caracter_e = especial
End Function

La función Caracter_e hace referencia a los caracteres especiales que hemos de colocar en la hoja1 o en cualquier otra:

API MATRIZ DE DISTANCIAS_2

Volviendo con la función Ruta, es interesante comentar acerca del formato de los datos:

  • Por un lado las unidades de tiempo, que vienen expresadas en segundos y que debemos dividir entre 84600 para obtener la fracción de día correspondiente. Y que luego tendremos que transformar con el formato personalizado a horas y minutos.
  • Por el otro, las unidades de distancia, expresadas en metros y que debemos dividir por 1000 para obtener los km.

Antes de nada configuramos en las columnas de cada dado su formato (esto es muy importante), y lo haremos personalizando el formato de las celdas de las columnas: Duración: h:mm “h” y Distancia: #.##0,00 “Km”

Ahora debemos crear una matriz que nos muestre la información de la función Ruta, para ello tenemos que seguir los siguientes pasos:

Primero debemos colocar en la primera celda de la matriz la función, es decir en “C2” ponemos =ruta(A2;B2), a continuación seleccionamos las dos celdas, la C2 y la D2 y pulsamos F2 en el teclado, seleccionamos la fórmula y luego pulsamos CTRL + SHIFT (mayús) + ENTER,  de forma que introducimos la matriz, quedando así los datos: {=ruta(A2;B2)}, tanto en la celda C2 como en D2,

API MATRIZ DE DISTANCIAS_3

Luego solo tenemos que arrastrar la fórmula hasta el fin de los datos:

API MATRIZ DE DISTANCIAS_4

Ahora que ya podemos comprobar que la información es correcta y todo funciona bien, vamos a utilizar una pequeña macro para mostrar el mapa con las rutas que hemos introducido, aquí únicamente crearemos una url a la que añadiremos los puntos de origen y destino. Tal y como tenemos los datos en la hoja solo vamos necesitar el primer punto de origen, y todas las celdas de la columna 2, es decir, A2, B2, B3, B4, B5, B6 y B7.

Esta sería la macro:
Sub Mapa()
Dim URL As String
With Sheets(1)
'Componemos la url para mostrar el mapa con la ruta.
URL = "https://www.google.com/maps/dir/" & .Range("A2") & "/" & .Range("B2") & "/" & .Range("B3") & "/" & .Range("B4") & "/" & .Range("B5") & "/" & .Range("B6") & "/" & .Range("B7")
ActiveWorkbook.FollowHyperlink URL, NewWindow:=False
End With
End Sub

Y este sería el mapa:

API_MATRIZ DISTANCIAS

Me gustaría indicar, que además de las distancias en coche, también se pueden extraer las distancias a pie o en bicicleta (en la macro lo comento). Si cuando realizáis el cambio os aparece un error en los datos, se debe a que no está disponible temporalmente o que no es posible realizar la ruta con dicho medio.

Por otra parte, si os habéis fijado, los datos de origen y destino pueden ser de varios tipos, nominales, añadiendo el código postal o también indicando las coordenadas.

Este un artículo meramente informativo y educativo desarrollado en esta web. En ningún momento Google ha colaborado en la creación de este código.

Y ahora, como siempre, os dejo el archivo de trabajo: API MATRIZ DE DISTANCIAS

 

Anuncios

66 pensamientos en “API MATRIZ DE DISTANCIAS EN VBA

  1. Buenos días,

    Unir excel con datos extraídos de la web es una pasada por la cantidad de posibilidades que ofrece. Generalmente no pasamos mas allá de obtención de cotizaciones de bolsa, pero como tu mismo has demostrado las posibilidades son infinitas, todo es cuestión de saber crear la consulta y saber dónde hacerla.

    Muchas gracias por el post, como siempre muy didáctico.

    Me gusta

  2. Hola, el programa funciona correctamente, lo único que Google tiene una limitación de 2500 búsquedas diarias,,,sobrepasado este limite tendríamos que esperar 24 horas para que el programa vuelva a funcionar, ya que Google bloquea las búsquedas.

    Funciona correctamente

    Me gusta

  3. Muchísimas gracias por el archivo. Lo abro en Mac pero no me funciona (aparece #VALUE!), crees que puede ser por la versión de Excel/Mac que tengo? La verdad es que no suelo trabajar con Excel y nunca he usado Macros, así que puede que esté haciendo algo mal, pero esto me viene como anillo al dedo para unos datos que necesito en R…

    Me gusta

    • Hola Ainhoa,

      Efectivamente la causa de que no funcione correctamente es que está programado para funcionar en windows. Seguramente el problema es que en Excel para MAC falte alguna de las librerías que utiliza la macro o simplemente algunos de los objetos definidos no están incluidos.

      No desarrollo en MAC y tampoco tengo posibilidad de probarlo para ver cuál es el error concreto que hace fallar la macro. La alternativa es que lo uses en un equipo con windows o en una máquina virtual que instales en mac con windows.

      La macro funciona (probada) en windows 7, 8.1, 10 y en versiones de Excel 2010, 2013 y 2016. Espero que te sirva de ayuda.

      Saludos.

      Me gusta

  4. Buenas tardes.
    Cuento con las características mencionadas de Windows y con la versión 2013 de excel, peo aun asi no me funciona lo desarrollado.
    Sabras cual puede ser el error? ya que esto es justo lo que necesito para un proyecto laboral..

    Desde ya muchísimas gracias!!

    Me gusta

    • Hola Matty,

      Debería funcionarte correctamente. El archivo lo has probado directamente de la descarga y no te funciona o lo has modificado el código y no te funciona.

      La versión utilizada de excel es la de 32 bits y si tienes la versión 2013 debería funcionar sin problemas.

      Necesitaría ver el archivo (si lo has modificado) para comprobar cual es el error.

      Por cierto, si pones más de 2.500 registros saltará un error, es el límite que tiene el API gratuita.

      Saludos.

      Me gusta

  5. Segu, muchas gracias por el rápido contacto!
    Retiro lo mencionado.. Fue un error de mi parte… Mil gracias por tanto aporte y por ser tan resolutivo!!!

    Saludos!!

    Me gusta

    • Hola Rafael‎

      Solo tienes que entrar en el código y en la línea que convierte metros a Km:‎
      Row(1, 2) = CLng(Val(1).NextSibling.Data) / 1000

      La sustituyes por esta, que pasa metros a millas:
      Row(1, 2) = CLng(Val(1).NextSibling.Data) * “0,000621371”

      A continuación, en la hoja en la columna de las distancias, las celdas le das formato personalizado: 0,00 “mi”

      Y ya tienes los datos en millas. Saludos!‎

      Me gusta

  6. Hola segu. Estoy tratando de hacer funcionar esta planilla que de conseguirlo me ahorraría horas de trabajo, por lo cual quería preguntar: en el trabajo tengo una compu con XP y excel 2003. No hubo forma de que funcione. En cambio en mi casa con 2013 y Windows 7 si lo hizo. Hay un problema de compatibilidad? Y la otra en la página de Google dice que hay que agregar una clave a la url para la solicitud. Yo cree la clave, pero es necesario agregarla? Y como quedaría?
    Te agradezco tu colaboración y prometo una donación ya que estoy aprendiendo muchas cosas que me estan siendo de utilidad. Saludos

    Me gusta

    • Hola Patricio,

      Es posible que se trate de un problema de versiones, sin embargo no tengo acceso a versión windows XP para probar y verificar que esto sea así.
      Por otra parte, las consultas tienen un límite diario de 2.500 búsquedas, una vez llegas a este limite tienes que esperar 24 horas a realizar otro.
      Si es un problema de la versión 2003 te lo comentaré mañana.
      Lo de la clave, no debería solicitarte ningún tipo de clave por mostrar el mapa, simplemente estamos abriendo un hipervínculo …
      Es muy probable que con el sistema operativo que tienes en el trabajo y la versión de excel no funcione.
      Lo he probado en windows vista, 7 y superior con Excel 2007 y superior y funciona correctamente.
      Cuando lo pruebe en 2003 con sistema operativo win7 te comento.

      Saludos.

      Me gusta

      • Muchas gracias. Si debe ser efectivamente problema de compatibilidad. Ya probé en otra máquina con Windows 7y funciona perfectamente. Gracias y contá con mi donativo. Muy útil tu trabajo.

        Me gusta

        • El problema de XP es que ya no recibe actualizaciones por parte de Microsoft y por otra parte algunos elementos del código hacen referencias a objetos que probablemente XP no soporte.

          Me alegro que hayas realizado la prueba y que funcione correctamente. Ahora a planificar un buen viaje (que el programa para controlar los km y el tiempo ya lo tienes).

          Saludos!!

          Me gusta

  7. Jaja ojalá se tratara de placer. Lo que estoy tratando de hacer es automatizar parte del trabajo que hago. Me dedico a contratar traslados para personal jerárquico de empresas. Y para pagar a quienes contrato necesito calcular los kilómetros de cada viaje, ya que se paga por kilómetro. Voy de s poco logrando eso. Ahora estoy trabajando en algo un poco mas complicado, espero poder lograrlo ya que significaría un importante ahorro de tiempo. Gracias nuevamente.

    Me gusta

  8. Hola, estoy haciendo una matriz de distancias, pero aplico la funcion “ruta” y me aparece la duración. Como lo puedo hacer para que me aparezca solo al distancia? tengo que modificar el codigo?
    Saludos

    Me gusta

    • Hola Carlos,

      Es necesario realizar algunas modificaciones en la función y también en la hoja (en la matriz). Te envío el archivo modificado (para que veas los cambios) a tu correo electrónico.

      Saludos.

      Me gusta

  9. Hola Segu, tengo un problema al copiar y pegar valores en la columna duración… Aparentemente 0:19 h no lo transforma a 19 minutos ni a 19/60 horas. Tienes alguna solución para esto?

    Le gusta a 1 persona

    • Hola Javier,

      Cuando dices que copias y pegas en la columna duración, a qué te refieres?. En columna duración y distancia no se pueden pegar valores (es una matriz y no se puede modificar.

      Ahora bien, si lo que haces es copiar los valores de la columna duración y pegarlos en otra columna, antes de realizar el pegado (valores) debes aplicar a toda la columna de destino un formato de especial: selecciona la columna, botón derecho y selecciona formato de celdas > ficha números > especial > y formato tipo: h:mm “h”

      Entonces tendrá el formato en horas y minutos.

      Saludos.

      Me gusta

  10. Segu, tengo el mismo problema. Tengo los valores como h:mm pero el valor no se guarda como minutos. Es decir, 0:14 debería ser 14 al copiar y pegar valores.
    Esto lo quiero usar con tu código para obtener una velocidad, el código retorna distancia y tiempo, por lo que al hacer distancia/tiempo quiero que devuelva la velocidad
    Gracias de antemano!

    Le gusta a 1 persona

    • Hola Javier,

      Te paso por correo la forma de hacerlo. Pero en resumen, debes crear una columna nueva, le das formato número e introduces esta fórmula: =D2/(24*C2) donde D2 es la celda con la distancia y C2 con el tiempo. Esto te dará el los Km/h.

      Luego si quieres que aparezca al final Km puedes hacerlo con un formato personalizado.

      Es un tema de formatos de celda.

      Saludos.

      Me gusta

  11. Hola Segu, primeramente agradecerte el que compartas tu trabajo que nos es muy útil. Si no es mucha molestia me podrías indicar que modificación he de hacer en la segunda macro para que en lugar de salir una ruta con los puntos enlazados, se plasme en el mapa una ruta por cada fila. Me explico el punto de destino el mismo en B2 pero desde varios puntos de partida. Como si quisiera ir a Barcelona desde Valencia, desde Bilbao y desde Sevilla.
    Saludos

    Me gusta

    • Hola Rafa,

      Lo que comentas se puede hacer, pero solo en parte, es decir, MAPS solo permite indicar un origen y un destino (o varios en la misma ruta), sin embargo no puedes realizar una ruta con diferentes orígenes a un único destino.

      Podrías intentar lo siguiente, (que en la matriz de distancia funcionaría correctamente), pero en MAPS (aunque lo dibuja correctamente, duplica los KM. Esto debes tenerlo en cuenta

      Lo primero es configurar la hoja de Excel, es decir, has de indicar en la columna A los orígenes, en A2 (Valencia), en A3 (Bilbao), en A4 (Sevilla) y en la columna B el destino, es decir, en B2, B3 y B4 (Barcelona).

      Luego modificas el código, te pongo solo la parte de la URL, así puedes ver el orden que deben llevar los datos

      URL = "https://www.google.com/maps/dir/" & .Range("A2") & "/" & .Range("B2") & "/" & .Range("A3") _
      & "/" & .Range("B3") & "/" & .Range("A4") & "/" & .Range("B4")

      Siempre ha de ser el inicio y luego el destino y así por fila.

      Espero haberte ayudado. De todas formas te envío el fichero modificado a tu correo. Saludos.

      Me gusta

    • Hola Sonii,

      Para poder incrustar una pagina web en una hoja de excel es necesario utilizar un control activeX (en este caso un WebBrowser). Sin embargo, esta solución no la recomiendo, además de los problemas que surgen con las alertas de los Scripts (son insufribles), el control webBrowser funciona con IE al que hace referencia VBA y en el caso de Maps es necesario que la versión utilizada de explorer sea la 11. Y para realizar este cambio es necesario introducirse en las librerías de windows.

      Es por eso mismo que directamente, desde la hoja “llamamos” al explorador que tengamos instalado en nuestro ordenador y mostremos la página.

      Es la mejor solución. De todas si deseas intentarlo, puedes echar un vistazo en este ejemplo:

      https://excelsignum.com/2013/08/25/insertar-navegador-web-en-hoja-excel/

      Saludos

      Me gusta

  12. Pingback: API GEOLOCALIZACIÓN EN VBA | EXCEL SIGNUM

      • Hola Kim,

        Me alegro que te funcione, sin embargo es extraño que tengas que marcar la referencia a XML, De hecho, en el código he creado el objeto precisamente para no tener que llamar a la biblioteca:
        Set xml = CreateObject("Microsoft.XMLHttp")
        Es decir, en Excel 2010, 2013 y 2016 funciona sin necesidad de referencias.

        Pero, si te funciona, perfecto!!

        Saludos.

        Me gusta

  13. Hola quiero hacer esto mismo con destinos en Colombia pero no me permite hacerlo me gustaría saber que debo modificar para que funcione con otros destinos ya que no soy experta en programación

    Me gusta

    • seria posible trazar varias rutas? bueno lo que quiero hacer es con municipios de Colombia, por ejemplo armenia-salento, armenia filandia y asi sucesivamente pero que me muestre todas las rutas en el mapa??

      Me gusta

      • Hola Daniela:

        Prueba es escribir Armenia, Colombia es decir, indicando el país en el que estás: Salento, Colombia etc. Acabo de probarlo y funciona perfectamente. A veces hay que detallar el país cuando en el mundo hay varios con el mismo nombre, ejemplo Armenia. Por eso debes ponerle la , Colombia.

        Saludos.

        Me gusta

  14. Hola, la planilla me funciona a la perfección, gracias por tu aporte, pero tengo la duda de si funciona dentro de una red de internet corporativa (de empresa), ya que manda error desde una red de este tipo, a diferencia de la red de mi casa donde corre perfecto.

    Me gusta

    • Hola Ivan:

      Para descartar que sea un tema de permisos, etc. Intenta usar el google maps desde internet en tu equipo en la empresa, si puedes usarlo no es un problema de conexión con internet.

      Otro problema podría ser las referencias que usa VBA y que en el Excel de tu empresa no estén recogidas.

      Por lo demás, no sabría decirte cual podría ser el problema.

      Saludos.

      Me gusta

  15. Hola Segu,

    Tengo 600 puntos geograficos , tiene algun tope de calculo por que hay celdas que me salen #¡VALOR! en ambas.

    Me colaboras.
    Grs.

    Me gusta

    • Hola Alexander:

      El problema que tenías es que las celdas que tenían error, no estabas seleccionando el origen y el destino, estabas seleccionando el destino y la duración y con eso la función nunca funcionará. Al arrastrar la fórmula se te ha movido el cursor.

      Saludos.

      Me gusta

    • Hola Manolo:

      Fácil, solo has de añadir en la URL que se eviten los peajes “avoid=tolls”. Sería así:

      URL = "https://maps.googleapis.com/maps/api/distancematrix/xml?origins=" & Origen & "&destinations=" & Destino & "&mode=&avoid=tolls"

      Espero que te sirva. saludos.

      Le gusta a 1 persona

  16. muchisimas gracias por contestar y sobre todo por contestar tan rapido, eres un crak es lo que necesitaba
    ando trabajando siempre en macros y formularios en excel por lo que ya nos cruzaremos.
    Muchas gracias otra vez y felicidades por tu aporte es super interesnte.

    Me gusta

  17. Hola,

    Cuando realizo click en “Habilitar contenido” automáticamente las celdas cambian a #VALOR!. ¿Qué puedo hacer para que funcione?
    Utilizo el excel 2013 y tengo windows 10.

    Es genial y muy útil tu aplicación. Idea muy práctica.

    Muchas gracias,
    Natalia

    Me gusta

    • Hola Natalia:
      La macro debería funcionarte sin problema. Lo que ocurre es que cuando la descargas de la web, en tu equipo salta la seguridad por las macro y por ser un archivo procedente de Internet, en el momento que habilitas contenido, la aplicación tarda un momento en volver a conectarse a google maps (y en ese momento es cuando aparece ese mensaje).

      Al cabo de unos segundos, debería funcionarte perfectamente.

      Saludos.

      Me gusta

      • Así lo he hecho pero me sigue indicando lo mismo en las columnas de duración y distancia. #¡VALOR!
        No consigo que funcione.

        Muchas gracias por tu atención.

        Me gusta

        • Hola Natalia:

          Pues no sé cual puede ser el problema, yo mismo he ejecutado el archivo en 2013 con Windows 10 y funciona correctamente. has probado a introducir una dirección o hacer doble clic sobre una de las direcciones existentes?.

          No se … la conexión a internet es correcta?.

          Prueba a ejecutarlo en otros equipos para ver si es un problema de tu ordenador (no debería, pero has de probar).

          Supongo que no estás introduciendo más de 2.500 puntos, dado que ese el el límite del API y cuando llega a ese límite se bloquea con ese tipo de error.

          Saludos.

          Me gusta

  18. Excelente el aporte. Realmente sos el único que encontré en la web que me salvo con esto.

    Solo tengo una consulta: En la columna distancia y tiempo me arroja el mismo valor.

    Como lo corrijo?

    Gracias!!!!

    Me gusta

    • Hola Agustín, acabo de ver el archivo que me has enviado. Existe un error, y es que no estás aplicando la formula matricial en el resultado. Lo explico claramente en el post. Es fundamental, de otra forma, los datos no sería correctos.

      Te lo envío con la fórmula introducida matricialmente:

      Aunque, lo único que deberías hacer es descartar el archivo de esta web y poner las direcciones, sin eliminar la matriz creada.

      Saludos y buenas vacaciones.

      Me gusta

¿Te ha gustado?. Deja un comentario

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s