lunes, 22 de octubre de 2012

VBA: UpdateLink - Actualizar vínculos en Excel.

Son muchas las veces que me consultan sobre la forma de Actualizar vínculos externos en nuestras hojas de Excel. Sabemos que disponemos de la herramienta de Edición de vínculos (ver), desde la cual podemos actualizar nuestros valores siempre que lo necesitemos.
Esta herramienta está pensada para aplicarla en aquellos casos que tenemos vínculos con otros libros de trabajo (en nuestro PC o en red) y que se encuentran cerrados, puesto que si estuvieran abiertos no se haría necesario forzar esa actualización.

Así que centrémonos, tenemos al menos dos libros de trabajo diferentes, en uno de ellos el origen de datos (VinculosOrigen.xlsx) y en otro el destino (VinculosDestino.xlsx), como vemos en la siguiente imagen:

Actualizar vínculos en Excel.



Si cerramos el libro VinculosDestino.xlsx y cambiamos nuestra celda de referencia, y cerramos y guardamos este libro origen. La siguiente vez que abramos el libro destino VinculosOrigen.xlsx, aparecerá por defecto un mensaje adviertiendo de la existencia de vínculos, y preguntando si es nuestro deseo actualizarlos:

Actualizar vínculos en Excel.


La celda vinculada no se actualizará hasta que Habilitemos esta opción. Es posible evitar esta pregunta, y que el libro automáticamente actualice al abrir los vínculos existentes. Para ello iremos a la ficha Datos > Conexiones > Editar vínculos, se abrirá una ventana diálogo, y en esta presionaremos el botón Pregunta inicial, y entre las tres opciones desplegadas, elegiríamos la de No mostrar la alerta y actualizar vínculos:

Actualizar vínculos en Excel.


Pero esto sólo surtiría efecto al abrir el fichero 'destino', y no cada vez que hubiera un cambio en el origen (algún otro usuario en red podría estar trabajando sobre él).

Llegamos entonces a la cuestión clave, qué ocurre si siempre quiero tener la certeza de trabajar con los últimos datos actualizados... y la respuesta es trabajar con una macro con un evento _Change (o _SelectionChange) en la hoja o el libro completo y el método UpdateLink.
Tenemos un par de opciones por delante, una es forzar una actualización de valores sobre un vínculo en concreto o bien la actualización de todos los vínculos existentes con cualquier libro.

Veamos el primer caso: Actualizar todos los vínculos del libro de trabajo. Para ello, insertamos el siguiente código VBA en la hoja donde exista el vínculo en la hoja concreta del VinculosDestino.xlsx:

Private Sub Worksheet_Change(ByVal Target As Range)
'actualizamos todos los vínculos existentes en el libro
ActiveWorkbook.UpdateLink Name:=ActiveWorkbook.LinkSources
End Sub


Con este código, con la macro de Excel, conseguimos que cada vez que se cambie o actúe sobre una celda de la hoja seleccionada (donde hemos incorporado el código) se Actualicen los valores de cualquier vínculo.

Para el segundo caso: Actualizar los vínculos concretos sobre una hoja. Para ello, insertamos el siguiente código VBA en la hoja donde exista el vínculo en la hoja concreta del VinculosDestino.xlsx:

Private Sub Worksheet_Change(ByVal Target As Range)
'actualizamos sólo el vínculo que nos interesa
ActiveWorkbook.UpdateLink Name:="E:\excelforo\VinculosOrigen.xlsx", Type:=xlExcelLinks
End Sub


Con este otro código únicamente actualizamos ese vínculo, los demás -si existieran-quedarían pendientes.

Por supuesto, cualquiera de estas acciones, ya sean las macros o la configuración de los vínculos a través de la herramienta Edición vínculos se realizaría en el fichero de destino...
Y mucho OJO con esto, ya que las actualizaciones requieren mucha memoria y podría paralizar tu trabajo normal...

36 comentarios:

  1. Hola Excelforo

    Muy Bueno este articulo, lo leí alguna vez pero no le había dado importancia, sin embargo me seria de muchísima utilidad, me surge una duda, si quiero actualizar todas las hojas de un libro, tendría que colocar esta Macro a cada una de las hojas o existe una forma que me reconozca todas las hojas del Libro y se actualicen los vínculos.

    Un Abrazo
    Lázaro.

    ResponderEliminar
    Respuestas
    1. Hola Lázaro!!
      sería suficiente colocarlo en el editor de VBA en ThisWorkbook.
      Un cordial saludo

      Eliminar
    2. Hola Ismael
      He probado con este código en ThisWorkbook pero no actualiza.

      Buceando por Intenet he encontrado la posible respuesta:
      - para insertar esta instrucción en ThisWorkbook, la línea tendría que quedar de la siguiente manera:
      Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

      De todos modos, muchas gracias por tus consejos.
      Roberto

      Eliminar
    3. Gracias por el aporte Roberto!

      Revisa en todo caso el procedimiento propuesto...
      todo lo subido en el blog está probado y verificado
      ;-)
      Un cordial saludo

      Eliminar
  2. como le puedo hacer si es un vinculo a un libro que esta protegido

    ResponderEliminar
    Respuestas
    1. Hola Alberto,
      indagaré un poco, pero creo que no se puede hacer más que introducir la contraseña del libro orogien cuando te la pida al actualizar.
      Slds

      Eliminar
  3. Este comentario ha sido eliminado por el autor.

    ResponderEliminar
  4. hola....
    tengo un problemita....
    tengo un libro llamdo formato en el cual ingreso unos datos de inventarios de una empresa de confeccion por procesos como corte empaque y demas.....
    asi mismo por cada proceso es un libro....y son los libros destino de el formato......y por ultimo tengo un informe semanal...... el problema es q solamente se me actualizan los datos en el informe semanal si todos los libros de los procesos estan abietos.... lo q realmente quiero es q se actualizen los datos del informe sin necesidad de abrir los procesos.....espero me haga entender ...gracias....

    ResponderEliminar
    Respuestas
    1. Hola Jakson,
      lo normal sería que accediendo a la herramienta Editar vínculos, y actualizándolos todos (ficha Datos > grupo Conexiones > botón Editar vínculos).
      Es cierto que en ocasiones, esta herramienta no funciona muy bien, por lo que nos vemos obligados a abrir los diferentes libros donde se encuentran los vínculos en origen.
      En definitiva, prueba (si no lo has hecho ya) a Actualizar desde la ventana Editar vínculos, y si aún así no te actualiza, quizá habría que forzar esto mediante un procedimiento VBA (una macro).
      Slds

      Eliminar
  5. Hola Ismael, una consulta, estos vinculos tambien funsionan en xls o xlsm?

    ResponderEliminar
    Respuestas
    1. Hola!!
      seguro en .xlsm (versiones 2007 o superiores), lo que no tengo muy claro es si funcionaría igual en .xls (versión 2003 o anteriores).
      Slds

      Eliminar
  6. es posible hacer que se actualice a cada cierto tiempo?

    un saludo

    ResponderEliminar
    Respuestas
    1. Hola que tal, espero te encuentres bien.
      Puedes combinar lo expuesto en esta macro con lo explicado del método .onTime en
      http://excelforo.blogspot.com.es/2013/06/vba-ejecutar-una-macro-de-excel-una.html

      Slds cordiales

      Eliminar
  7. Hola Ismael Gracias por tu respuesta, me sirvio pero te comento, como puedo ademas hacer eso mismo pero que se repita a cada 10 segundos por ejemplo, no solo que se ejecute 10 secundos posteriores a la apertura del fichero?

    Gracias

    ResponderEliminar
    Respuestas
    1. Hola,
      bueno, puedes aplicar un ciclo entre procedimientos Sub para que se actualice cada equis segundos.
      Echa un vistazo a este link de Microsoft
      http://support.microsoft.com/default.aspx?scid=kb;en-us;213288&Product=xlw2K
      Seguramente te dará los pasos que necesitas.
      Slds

      Eliminar
  8. Hola Ismael.
    Estoy intentando aplicar tu gran consejo pero al realizar los cambios en la pestaña donde he pegado el código, me da un error:

    "Error en el método 'UpdateLink' de objeto '_Workbook'"

    Este error también me da si ejecuto la siguiente macro:

    Dim w As Workbook

    For Each w In Application.Workbooks
    If w.Name = "(fichero que quiero actualizar.xls)" Then
    ActiveWorkbook.UpdateLink Name:=ActiveWorkbook.LinkSources
    End If
    Next w
    Lo que quiero decirle es que de los libros abiertos, actualízame el que te paso en esta sentencia: w.Name = "(fichero que quiero actualizar.xls)"

    Gracias por tu ayuda;
    Roberto

    ResponderEliminar
    Respuestas
    1. Hola Roberto,
      qué versión de Excel tienes??.. aunque diría que no tiene nada que ver por que este método .UpdateLink creo recordar existe desde versiones anteriores a 2003.

      He probado tu segundo código con Excel 2010 y 2007 y funciona correctamente.
      Si quieres envíame el fichero y lo reviso.
      Slds

      Eliminar
    2. Gracias Ismael por tu tiempo. Creo que era un tema tan tonto, tan tonto como que no había definido la variable w en el código del fichero.

      Felicitaciones por el blog...

      Eliminar
  9. Hola Ismael soy novata en todo este tema, queria preguntarte si existe una macro en la que al oprimir un boton unicamente abra la ventana de modificar vínculos para que el usuario seleccione las carpetas común y corriente , sin tener que escribir la ruta en visual, solo necesito que me abra la ventanita de modificar vinculos.
    Mil gracias por tu atención

    ResponderEliminar
    Respuestas
    1. Hola,
      no creo exista una forma con macros para abrir la ventana, pero bastaría presionaras Alt+d+i para que se abriera.. sin macros y sin botón.
      Espero te sirva.
      Un cordial saludo

      Eliminar
    2. Gracias por tu ayuda. Me fue muy útil

      Eliminar
  10. HOLA, CUAL SERÍA EL CODIGO QUE ME FALTA PARA QUE ACTUALIZE EL VÍNCULO CON UN LIBRO QUE CONTIENE CLAVE DE APERTURA, OJO, APERTURA, NO LECTURA.
    ActiveWorkbook.UpdateLink Name:= _
    "C:\Users\prueba.xlsm",Type:=xlExcelLinks

    ResponderEliminar
    Respuestas
    1. Hola, que tal...
      diría que primero tendrías que abrir el libro en cuestión
      Workbooks.Open Filename:="C:\Users\prueba.xlsm", password:="1111"
      y a continuación
      Workbook("Trabajo.xlsx").UpdateLink Name:= _
      "C:\Users\prueba.xlsm",Type:=xlExcelLinks
      y acabar cerrando el libro de prueba.xlsm

      Diría que debe funcionar

      slds

      Eliminar
  11. Ayuda, no me actualiza, el codigo de la macro se pone en el libro raiz? osea donde se jala la informacion?.

    Por favor necesito su ayuda :(

    ResponderEliminar
    Respuestas
    1. Hola,
      el código se coloca en la hoja del libro donde se encuentra el vínculo, es decir, NO donde se encuentra la información, sino en el destino.
      Saludos

      Eliminar
    2. Muchas gracias por tu pronta respuesta, mi inconveniente es el siguiente:
      Tengo una hoja de excel (datos) en donde plasmo las actividades que debe realizar el area de almacen, estas actividades las vincule en otro archivo (almacen) para que el area de almacen vea las actividades, pero no se atualiza, en donde estoy guardando los 2 archivos es una unidad de red..entonces la macro la coloco en el archivo (almacen)??

      Gracias por tu apoyo

      Eliminar
    3. Es correcto,
      el código deberá estar en el archivo 'Almacén'... pero con esta herramienta siempre digo lo mismo (puedes leer comentarios anteriores), falla bastante y sólo funciona al 100% cuando libro origen y destino están abiertos...
      Saludos

      Eliminar
    4. Muchas gracias, de echo tengo los 2 libros abiertos, lo raro es que si los abro en la misma PC se actualizan automaticamente, pero si abro los documentos en PC´s diferentes tengo que cerrar y volver a abrir el documento origen (almacen) para que se vean los cambios reflejados :(, alguna opcion que me recomiendes?

      Eliminar
    5. No es raro lo que te ocurre.. ya te digo que esto es algo muy habitual (por desgracia), como digo la única forma segura es tener abierto los dos libros (obviamente desde el mismo equipo), sólo así Excel asegura rutas..
      Podrías tener como Libro compartido el origen, así diferentes usuarios, en diferentes equipos, podríais tener el Libro abierto sin problemas...

      Slds

      Eliminar
  12. Este comentario ha sido eliminado por el autor.

    ResponderEliminar
  13. Gracias!!! me viene muy bien!!!....hace poco me paso algo relacionado.......Justamente porque yo ya sospechaba que editar vínculos no funciona muy bien y para asegurar que estaba vinculado con el archivo origen correcto . volví a seleccionar el archivo origen....pero del apuro seleccioné personal.xlsb , en vez del libro de origen , y se armó un lío . Voy a probar!!!!

    ResponderEliminar
  14. Buenas tardes, tengo un pequeño problema, me explico.
    Tengo un excel que recopila datos de varios archivos que se van creando automaticamente al mes y el problema es que yo tengo las formulas con los link metidos en las celdas y una macro para que vaya actualizando las formulas y escogiendo cada dia el archivo que corresponde (ejemplo 20151207+"nombre de archivo") y el prblea es que por ejemplo las casillas del resto del mes me pide al archivo y tengo que cancelarlas manualmente para que deje los link hasta que el archivo se cree.
    existe alguna manera de que se actualicen los link solos y las ventanas emergentes se cancelen si no existe el archivo?
    actualizo con "activeworkbook.updatelink name:=activeworkbook.linksources"

    Gracias de antemano

    ResponderEliminar
    Respuestas
    1. Hola Miguel,
      podrías controlar los errores con una rutina
      On error resume next...
      '[resto de código que genera el problema]
      On error goto 0

      espero haberte entendido bien
      Saludos

      Eliminar
  15. buenas amigo lo que necesito es que en esta formula:
    Sub condicional()
    salida = " "
    valor = Range("A1").Value + 1
    If valor > 11 Then
    salida = "X"
    Else
    If valor = 10 Then
    salida = "0"
    Else
    If valor <= 9 Then
    salida = " "
    Else
    End If
    End If
    End If
    Range("A2") = salida

    End Sub
    funcione en toda la hoja de trabajo en excel cual deberia ser el rango para especificar y ademas como colocar el valor cuando es menor que 9 ya que he intentado varias opciones y no me da el resultado de la suma
    de antemano gracias por la atencion prestada

    ResponderEliminar