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...

51 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
  16. Mi inquietud es cómo puedo abrir el archivo destino, con vinculos a otro archivo origen, pero sin tener que abrir el origen??

    ResponderEliminar
    Respuestas
    1. Hola Linareca, qué tal estás?
      Un placer saludarte igualmente.
      No es necesario abrir el archivo origen del vínculo para abrir el fichero final de trabajo... ni siquiera para actualizar los valores.
      ¿Te ha exigido Excel en algún momento tal cosa?
      Slds

      Eliminar
  17. Hola Ismael
    Primero de todo. Genial foro éste y otros muchos con los que ayudáis a novat@s como yo.

    Mi problema es el siguiente. Necesito actualizar en un fichero excel uno sólo de los muchos links que tiene. No me vale abrirlo, porque pesa tanto, que tarda demasiado.

    Por ello he usado tu macro:

    ActiveWorkbook.UpdateLink Name:= _
    "D:\Users\Sonia\Documents\04.Apr\Forecast\VCEAA Forecast 0417.xlsx", Type:= _
    xlExcelLinks

    Todo correcto, si no fuera porque tanto la carpeta (04.Apr) como el fichero (0417) van a ir cambiando el nombre cada mes y no me va a valer sin entrar a editar la macro. ¿Es posible? ¿Se te ocurre algo?

    Intento aplicar lo mismo que he hecho para abrir esta carpeta o fichero que varia de nombre cada mes, que es linkear la ruta y nombre de los ficheros a un fichero excel que uso de base donde si establezco el nombre correspondiente del mes, pero no me funciona cuando trato de aplicarlo a "Updatelink Name" con algo así como

    ActiveWorkbook.UpdateLink Name:= _
    strPath3 & strFile3 & ".xlsx", Type:= _
    xlExcelLinks

    Espero haberme explicado. Muchas gracias por adelantado. Saludos!

    ResponderEliminar
    Respuestas
    1. Hola Sonia, y gracias pro tus palabras.
      La idea que tienes es buena, y la que yo haría...
      ActiveWorkbook.UpdateLink Name:= _
      strPath3 & strFile3 & ".xlsx", Type:= _
      xlExcelLinks
      asegúrate que tus variables de ruta y file tienen dispuestos correctamente los separadores \ de dirección.
      Debería funcionar sin problemas
      Saludos

      Eliminar
    2. Muchas gracias Ismael por tu pronta respuesta
      Tienes razón. Mi idea era perfecta por lo que veo, salvo por el separador que me comentabas.
      Revisado. Funciona perfectamente!
      Ahí queda el código por si a alguien le fuera útil esta combinación.
      Gracias! Saludos!

      Eliminar
  18. Ismael, perdona, se me ocurre otra duda
    ¿habría alguna manera de programar con una macro los cambios (no simple actualización) de links de ficheros?
    Me refiero. Haciendo un trabajo previo en el que describa en una hoja excel:
    - el link actual que contiene el libro (ruta&nombre del fichero)
    - y el nuevo link por el que debe cambiar (ruta&nombre del fichero)
    Y luego programar una macro con estos datos.

    He buscado por la red, y no encontré una ligera idea de si es posible y cómo.
    Muchas gracias. Saludos!

    ResponderEliminar
    Respuestas
    1. Hola Sonia,
      me alegro te sirviera el punto anterior.
      En cuanto a la segunda cuestión, sí sería posible.. fíjate en este ejemplo
      http://excelforo.blogspot.com.es/2015/09/vba-el-metodo-movefolder-o-como-cambiar.html, aunque es otra técnica, sería similar para el cambio de los vínculos...
      Saludos

      Eliminar
    2. Hola Ismael,
      pero el link al que me remites es más bien para cambiar ficheros de ubicación, ¿cierto? ¿o es que hay una manera de combinar ambas técnicas?
      Yo lo que necesito es, siguiendo tu ejemplo, en el fichero "VinculosDestino.xlsx" cambiar el link que tiene a "E:\excelforo\VinculosOrigen.xlsx" por "E:\excelforo\2\VinculosOrigen2.xlsx" a través de una macro. Lo que manualmente se ejecuta con Data/Edit Links/Change source.
      Yo lo necesitaría aplicar en ficheros que contienen unos 10-20 links.
      Muchas gracias. Saludos,

      Eliminar
    3. Hola Sonia,
      me refiero a que puedes combinar ambas ideas para realizar el cambio que deseas.
      De todas formas tomo nota e intentaré publicar un post con la solución
      Un saludo

      Eliminar
    4. hola!!, yo estoy con algo parecido....Mi vinculo siempre se llama igual pero cambia la ruta según la computadora que use..... a veces esta en C:\Users\MILINK.xlsm otras D:\Usuario\MILINK.xlsm y otra C:\auxiliar\MILINK.xlsm .
      Necesitaria que busque cual es la ruta actual, que cambie el vinculo y luego actualice (esto ultimo es más facil)...Mil gracias!!!!!.....siempre consulto tu blog!!!!

      Eliminar
    5. Hola,
      ¿cambia la ruta según la computadora?, te refieres a que el usuario tiene rutas distintas?...
      Me parece muy extraño que cambie por si mismo.. nunca me encontré tal cosa :(
      No creo exista una manera rápida para que desde Excel localicemos un fichero por el océano de carpetas y subcarpetas de las distintas redes o unidades del PC buscando la ruta de un fichero en concreto.

      El editor de vínculos nos dirá la ubicación del vínculo que existía en el momento de la creación.. y solo a partir de eso podemos gestionar o cambiar las rutas.

      Siento no poder decirte nada más

      Un cordial saludo

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

      Eliminar
  19. gracias por contestar!!!.Exacto: hay una ruta en la creación del archivo...que no sera la misma al cambiar de compu...y desde ahí, comparar con las rutas de las otras compus (que seran siempre las mismas, creadas esperando recibir el vinculo y los archivos a trabajar)

    Seria algo como:

    "verificar si la ruta (o directorio) existe" ..........(algunas compus tienen unidad D, en otras hay varios usuarios, en algunas seré administrador, en otras invitada, etc)
    "cambiar en el libro de trabajo actual el vinculo a la nueva ruta" (el vinculo se llama igual "milink.xlsm", lo que varia es la ubicación en cada compu)
    "actualizar"
    "grabar"

    no tengo en red, sino que voy copiando para trabajar segun la compu que tenga disponible.
    Es tremendo el trabajo de cambiar manualmente los libros vinculados , lo cual se hace exitosamente, por supuesto...buscando mi archivo milink.xlsm en la ruta de esta compu.....

    otra forma de explicarlo seria decir que quiero preparar un grupo de archivos y su vinculo "portables", para llevar adonde quiera....
    Mil gracias!!!!

    ResponderEliminar
  20. bueno, me parece que funciona mi experimento...si hay otra forma, please soy todo oidos...
    Al abrir el archivo y no encontrar el vinculo dice el mensaje de alerta: "este libro contiene uno o más vinculos que no se pueden actualizar" y el resto, pero si hago click en "continuar" , sí ejecuta mi macro!

    Sub cambialinks()
    Application.DisplayAlerts = False
    Application.AskToUpdateLinks = False 'esta instruccion no hace nada!
    On Error GoTo error_sub

    Dim fso As Object

    'objeto FSO para funciones FileExists y FolderExists
    Set fso = CreateObject("Scripting.FileSystemObject")


    rutaProg1 = "C:\Users\"
    rutaProg2 = "D:\Usuario\"
    rutaProg3 = "C:\auxiliar\"

    If fso.FolderExists(rutaProg1) = True Then
    nombreruta = rutaProg1
    Else
    If fso.FolderExists(rutaProg2) = True Then
    nombreruta = rutaProg2
    Else
    If fso.FolderExists(rutaProg3) = True Then
    nombreruta = rutaProg3

    End If
    End If
    End If

    MsgBox "actual directorio de programa " & nombreruta


    'el asunto es de donde está viniendo el archivo: si de Prog1, de Prog2 o Prog3 ?
    'uso el error como verificacion del link que tiene guardado el archivo

    On Error Resume Next

    ChDir nombreruta
    ActiveWorkbook.ChangeLink Name:= _
    "C:\Users\MILINK.xlsm", NewName _
    :="MILINK.xlsm", Type:=xlExcelLinks

    On Error Resume Next

    ChDir nombreruta
    ActiveWorkbook.ChangeLink Name:= _
    "D:\Usuario\MILINK.xlsm", NewName _
    :="MILINK.xlsm", Type:=xlExcelLinks

    On Error Resume Next

    ChDir nombreruta
    ActiveWorkbook.ChangeLink Name:= _
    "C:\auxiliar\MILINK.xlsm ", NewName _
    :="MILINK.xlsm", Type:=xlExcelLinks

    GoTo limpiavariables

    error_sub:
    MsgBox "error al cambiar vinculo"

    limpiavariables:

    Set rutaProg1 = Nothing
    Set rutaProg2 = Nothing
    Set rutaProg3 = Nothing
    Set fso = Nothing
    Set nombreruta = Nothing

    End Sub

    sigo probando........no canto victoria aun
    gracias!!!

    ResponderEliminar
    Respuestas
    1. Este comentario ha sido eliminado por el autor.

      Eliminar

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