Obtener el nombre y la ruta de las conexiones de un libro de Excel VBA

Problema

Queremos obtener los nombres y rutas de las conexiones de un libro de Excel mediante VBA, sin necesidad de ir a la pestaña Datos, y ñuego a Conexiones.

Solución

Mediante código VBA podemos extraer el nombre y la ruta de las conexiones existentes en un libro. En el prime bucle obtenemos los nombres y en el segundo la ruta. Este segundo bucle, que incluye la propiedad TextConnection sólo funciona con Excel 2013 y versiones posteriores.

Sub ObtenerNombreRutaConexion()
    Dim conn As WorkbookConnection
    For Each conn In ActiveWorkbook.Connections
      Debug.Print conn.Name
    Next conn
    For Each conn In ActiveWorkbook.Connections
      Debug.Print conn.TextConnection.Connection
    Next conn
End Sub

Resultado

En la ventana Inmediato del editor (VBE) obtendremos el nombre y la ruta de las conexiones.

Entradas relacionadas

• Conectar Excel a una base de datos en Access
• Importar ficheros CSV en Excel mediante VBA

No es posible editar o actualizar los vínculos o el origen de datos

Cuando una hoja está protegida, al abrir Editar vínculos (en la ficha Datos, grupo Conexiones) las opciones Actualizar valores, Cambiar origen y Romper vínculos, aparecen desactivadas.

Hay que quitar la protección usando el comando Desproteger hoja (en la ficha Revisar, grupo Cambios) para poder editar, actualizar o romper los vínculos.

Ocurre exactamente lo mismo si tratamos de actualizar un origen de datos y la hoja está protegida. Al presionar sobre la tabla con el botón secundario del ratón, en el menú contextual aparecerá desactivada la opción Actualizar. Y, si en la ficha Datos, presionamos Actualizar todo, nos aparecerá el siguiente mensaje.

Tal y como nos advierte, si la hoja está protegida con contraseña, deberemos suministrarla al Desproteger hoja. Si no, no podremos editar o actualizar la conexión con el origen de datos. Sí que podremos quitar la conexión, aunque la hoja esté protegida y desconozcamos la contraseña.

Vínculos externos rotos al mover el libro de destino en Excel

Cuando Excel no puede actualizar los vínculos externos, nos muestra el siguiente mensaje alertando de la imposibilidad de actualizarlos. 

Podemos hacer clic en Modificar vínculos para editar las rutas de los libros de origen y en principio el problema estaría resuelto. No obstante, cuando los libros de destino y origen están ubicados en la misma carpeta, si copiamos el libro de destino a otra carpeta, se romperán los vínculos. En tal caso tendremos que editar los vínculos de nuevo.

¿Cómo es posible? Están correctamente vinculados y en ubicación indicaba la ruta completa del libro de origen. Si no cambia la ubicación del libro de origen de lugar, ¿por qué se rompen los vínculos?

La explicación se encuentra en cómo Excel almacena las rutas de acceso al vínculo. Excel básicamente sigue una serie de reglas para almacenar la ruta de acceso a un libro vinculado, guardando los vínculos de forma relativa siempre que sea posible. Es decir, no guarda la ruta completa al libro de origen: "si el archivo vinculado y el archivo de datos de origen están en la misma carpeta, se almacena sólo el nombre de archivo."

Al mover el libro de destino, se rompen los vínculos con los libros de origen que estaban en la misma carpeta del libro de destino. Excel busca esos ficheros en la nueva ruta del libro de destino y, al no encontrarlos, no puede actualizarlos. Para solucionarlo, tenemos dos opciones:

1. Copiamos esos libros de origen en la nueva ubicación del libro de destino para que continúen juntos.
2. Con el libro de destino abierto, guardar como en la nueva ubicación y se actualizarán los enlaces adecuadamente.

Por ello es recomendable separar los libros de origen y de destino. Así, si necesitáramos mover el libro de destino a otra ubicación, no tendremos problemas al actualizar los vínculos. Por ejemplo:

Por un lado, los ficheros de origen en la carpeta de su semana correspondiente. Y por otro, fuera de la carpeta, los ficheros de destino. Cuando los ficheros de destino no necesiten actualizarse más, podremos ubicarlos en su carpeta semanal.