¿Cómo puedo actualizar los archivos Microsoft Excel sin tener que hacerlo manualmente abriéndolos?
Mi nombre es Germán Peña. Gracias por ofrecerte en red a ayudar. Mi problema es el siguiente.
Actualmente estamos trabajando en un proyecto para la industria petrolera que tiene 150 archivos de excel vinculados entre ellos. Hablamos de más de 500 vínculos (aprox.). Son cálculos que se interelacionan entre sì. Mi pregunta es ¿cómo puedo hacer para que todos se actualicen constantemente sin tener que estar actualizando abriendo cada archivo? ¿Una posible solución es abrirlos todos al mismo tiempo?
Otra pregunta es cuando copio el mismo proyecto en otra carpeta, no todos los archivos combian la dirección de la vinculación, ¿cómo hago?
Tenemos Windows 2002 con Office XP.
Actualmente estamos trabajando en un proyecto para la industria petrolera que tiene 150 archivos de excel vinculados entre ellos. Hablamos de más de 500 vínculos (aprox.). Son cálculos que se interelacionan entre sì. Mi pregunta es ¿cómo puedo hacer para que todos se actualicen constantemente sin tener que estar actualizando abriendo cada archivo? ¿Una posible solución es abrirlos todos al mismo tiempo?
Otra pregunta es cuando copio el mismo proyecto en otra carpeta, no todos los archivos combian la dirección de la vinculación, ¿cómo hago?
Tenemos Windows 2002 con Office XP.
6 Respuestas
Respuesta de electrodo
1
En este caso debemos hacer una fórmula remota.
¿Qué es una fórmula remota?
Bueno, las fórmulas remotas permiten extraer valores de otras hojas del mismo libro o de otros archivos de excel y que se actualicen directamente.
Ejemplo, supongamos que en el libro1 y en la celda c11 tienes un valor de $200, y en la celda d11 del libro2 quieres que este ese mismo valor, entonces haz lo siguiente
En el libro2 en la celda d11 escribe:
=[(libro1.xls)hoja1!d11]
Esto nos permitiría que cad vez que en el libro 1 cambiemos los datos, en el libro dos también se cambiaran.
Ahora esta es la sintaxis de la fórmula
=[(nombre del archivo.extension)nombre de la hoja!celda]
¿Qué es una fórmula remota?
Bueno, las fórmulas remotas permiten extraer valores de otras hojas del mismo libro o de otros archivos de excel y que se actualicen directamente.
Ejemplo, supongamos que en el libro1 y en la celda c11 tienes un valor de $200, y en la celda d11 del libro2 quieres que este ese mismo valor, entonces haz lo siguiente
En el libro2 en la celda d11 escribe:
=[(libro1.xls)hoja1!d11]
Esto nos permitiría que cad vez que en el libro 1 cambiemos los datos, en el libro dos también se cambiaran.
Ahora esta es la sintaxis de la fórmula
=[(nombre del archivo.extension)nombre de la hoja!celda]
Respuesta de fejoal
2
Vuelto de mi fin de semana, acabo de ver tu pregunta.
Habrá que admitir, con todo, que la posibilidad de vincular archivos en MS Excel es una gran ayuda. Sin embargo el proceso de actualización de tales vínculos -con archivos cerrados- es un proceso realmente lento, más aún si tales vínculos son abundantes.
En el escenario que planteas, sería recomendable abrir los archivos vinculados y que se actualicen con el recalculo.
Pero la apertura conjunta de 150 archivos -estimo- bloquearía tu equipo...
Considero que puede haber un problema de diseño o arquitectura de los archivos, pero sin conocer lo que hacen, esto sería una afirmación temeraria, cuando menos.
Personalmente, trazaría un diagrama de la cadena de vinculaciones. Esto es: que archivo debe estar actualizado primero para que otro/s lo tome/n.
Luego abriría un grupo de archivos primarios y los grabaría como "área de trabajo". Esto hace que al abrir una de ellas, se abran -y actualicen- los archivos correspondientes. Luego grabo y cierro, y abro otra área con los archivos secundarios. Y así sucesivamente.
Esta misma rutina se puede automatizar con una macro que podría residir en un archivo auxiliar al efecto de que haga las actualizaciones correspondientes.
Armados los grupos de actualización, simplemente tendrías que usar la grabadora de macros y ejecutar paso a paso todo el proceso una vez. Al terminar de actualizar, detienes la grabadora y habrás obtenido un código de Visual Basic -en el archivo auxiliar donde empezaste la grabación- que repetirá ese proceso cada vez que lo ejecutes.
Espero que esto te haya orientado mínimamente.
Cualquier duda, preguntame nuevamente.
Un abrazo!
Fernando
*==========================================*
La presente respuesta ha demandado un tiempo de lectura,
análisis, elaboración y redacción. A cambio, simplemente,
Pretendo que te tomes un minuto para FINALIZARLA, si
Hubiera satisfecho -en alguna medida- tu requerimiento.
En caso contrario, puedes preguntarme nuevamente
Aclarando qué entendí mal o qué faltó.
Habrá que admitir, con todo, que la posibilidad de vincular archivos en MS Excel es una gran ayuda. Sin embargo el proceso de actualización de tales vínculos -con archivos cerrados- es un proceso realmente lento, más aún si tales vínculos son abundantes.
En el escenario que planteas, sería recomendable abrir los archivos vinculados y que se actualicen con el recalculo.
Pero la apertura conjunta de 150 archivos -estimo- bloquearía tu equipo...
Considero que puede haber un problema de diseño o arquitectura de los archivos, pero sin conocer lo que hacen, esto sería una afirmación temeraria, cuando menos.
Personalmente, trazaría un diagrama de la cadena de vinculaciones. Esto es: que archivo debe estar actualizado primero para que otro/s lo tome/n.
Luego abriría un grupo de archivos primarios y los grabaría como "área de trabajo". Esto hace que al abrir una de ellas, se abran -y actualicen- los archivos correspondientes. Luego grabo y cierro, y abro otra área con los archivos secundarios. Y así sucesivamente.
Esta misma rutina se puede automatizar con una macro que podría residir en un archivo auxiliar al efecto de que haga las actualizaciones correspondientes.
Armados los grupos de actualización, simplemente tendrías que usar la grabadora de macros y ejecutar paso a paso todo el proceso una vez. Al terminar de actualizar, detienes la grabadora y habrás obtenido un código de Visual Basic -en el archivo auxiliar donde empezaste la grabación- que repetirá ese proceso cada vez que lo ejecutes.
Espero que esto te haya orientado mínimamente.
Cualquier duda, preguntame nuevamente.
Un abrazo!
Fernando
*==========================================*
La presente respuesta ha demandado un tiempo de lectura,
análisis, elaboración y redacción. A cambio, simplemente,
Pretendo que te tomes un minuto para FINALIZARLA, si
Hubiera satisfecho -en alguna medida- tu requerimiento.
En caso contrario, puedes preguntarme nuevamente
Aclarando qué entendí mal o qué faltó.
Respuesta de ttella
2
Me parece muy interesante tu pregunta. Efectivamente en Excel tenemos un problema al vincular varios libros seguidos, ya que sólo se actualizan los libros inmediatamente vinculados. No los que van más allá en la cadena de vínculos.
Me hablas de un proyecto muy grande. Lamentablemente, la idea de tener tantos archivos que se aportan información entre sí no es buena. Sería mucho más fácil tener muy pocos libros con muchas hojas distintas.
La solución que propones te puede funcionar. Pero debes tener en cuenta que sólo debe tener acceso a los libros un usuario.
Yo te diría que automatizaras la apertura de libros a través de una macro.
Para ello lo primero es hacerte un árbol con los archivos que aportan los datos primero, bajando hasta aquellos que no aportan datos a ningún libro pero que extraen su información de otros.
Depende del archivo, pero pocos ordenadores soportan que se abran 150 archivos a la vez.
Por tanto tendrás que preparar una macro que abra y cierre los libros de una forma limpia y por el orden que te interesa.
He preparado un ejemplo para ponerlo más claro. Tengo 4 libros llamados:
Primero.xls
Segundo.xls
Tercero.xls
Cuarto.xls
En A1 de primero tengo el siguiente vínculo:
='C:\Mis documentos\[segundo.xls]Hoja1'!$A$1
En A1 de segundo tengo =+'C:\Mis documentos\[tercero.xls]Hoja1'!$A$1
En A1 de tercero tengo =+'C:\Mis documentos\[cuarto.xls]Hoja1'!$A$1
Y por fin, en cuarto.xls, tengo en A1 un valor cualquiera para probar.
Yo crearía en primero (en tu caso lo crearía en el Libro de Macros Personal para poder tener acceso desde cualquier archivo) una macro como la que sigue:
Sub VINCULO()
Application.ScreenUpdating = False
Workbooks.Open "cuarto.xls", 2, , , , , True
Workbooks.Open "tercero.xls", 2, , , , , True
Workbooks.Open "segundo.xls", 2, , , , , True
Workbooks("cuarto.xls").Close True
Workbooks("tercero.xls").Close True
Workbooks("segundo.xls").Close True
Application.ScreenUpdating = True
End Sub
Lo primero que hace es bloquear la pantalla hasta que termine. Lo segundo abrir los libros por orden diciendo que actualicen todos sus vínculos y que ignoren mensajes de "Recomendado sólo lectura" (por si acaso).
Lo tercero es cerrar todos los libros grabando los cambios y sin preguntar.
Lo cuarto es restablecer la actualización de la pantalla.
Esta macro deberías ejecutarla cada vez que te interesara tener el dato real en el libro primero.
En tu ejemplo, deberás abrir y cerrar archivos según el orden que se necesite para que se actualicen los vínculos (teniendo en cuenta que cuando se actualiza un vínculo Excel abre automáticamente los libros inmediatos y los vuelve a cerrar, con lo que te ahorras varias líneas de código).
A tu segunda pregunta, la respuesta es que tienes que mantener la estructura relativa de tus carpetas. Es decir, si tuvieras primero.xls en c:\ y segundo.xls en c:\toni para moverlos a c:\carpeta deberías mover segundo a c:\carpeta\toni, y no a c:\carpeta directamente. Si conservas la estructura relativa no tendrás problemas.
Espero haberte ayudado, y siento el retraso... cada vez son más las preguntas.
Un saludo,
Me hablas de un proyecto muy grande. Lamentablemente, la idea de tener tantos archivos que se aportan información entre sí no es buena. Sería mucho más fácil tener muy pocos libros con muchas hojas distintas.
La solución que propones te puede funcionar. Pero debes tener en cuenta que sólo debe tener acceso a los libros un usuario.
Yo te diría que automatizaras la apertura de libros a través de una macro.
Para ello lo primero es hacerte un árbol con los archivos que aportan los datos primero, bajando hasta aquellos que no aportan datos a ningún libro pero que extraen su información de otros.
Depende del archivo, pero pocos ordenadores soportan que se abran 150 archivos a la vez.
Por tanto tendrás que preparar una macro que abra y cierre los libros de una forma limpia y por el orden que te interesa.
He preparado un ejemplo para ponerlo más claro. Tengo 4 libros llamados:
Primero.xls
Segundo.xls
Tercero.xls
Cuarto.xls
En A1 de primero tengo el siguiente vínculo:
='C:\Mis documentos\[segundo.xls]Hoja1'!$A$1
En A1 de segundo tengo =+'C:\Mis documentos\[tercero.xls]Hoja1'!$A$1
En A1 de tercero tengo =+'C:\Mis documentos\[cuarto.xls]Hoja1'!$A$1
Y por fin, en cuarto.xls, tengo en A1 un valor cualquiera para probar.
Yo crearía en primero (en tu caso lo crearía en el Libro de Macros Personal para poder tener acceso desde cualquier archivo) una macro como la que sigue:
Sub VINCULO()
Application.ScreenUpdating = False
Workbooks.Open "cuarto.xls", 2, , , , , True
Workbooks.Open "tercero.xls", 2, , , , , True
Workbooks.Open "segundo.xls", 2, , , , , True
Workbooks("cuarto.xls").Close True
Workbooks("tercero.xls").Close True
Workbooks("segundo.xls").Close True
Application.ScreenUpdating = True
End Sub
Lo primero que hace es bloquear la pantalla hasta que termine. Lo segundo abrir los libros por orden diciendo que actualicen todos sus vínculos y que ignoren mensajes de "Recomendado sólo lectura" (por si acaso).
Lo tercero es cerrar todos los libros grabando los cambios y sin preguntar.
Lo cuarto es restablecer la actualización de la pantalla.
Esta macro deberías ejecutarla cada vez que te interesara tener el dato real en el libro primero.
En tu ejemplo, deberás abrir y cerrar archivos según el orden que se necesite para que se actualicen los vínculos (teniendo en cuenta que cuando se actualiza un vínculo Excel abre automáticamente los libros inmediatos y los vuelve a cerrar, con lo que te ahorras varias líneas de código).
A tu segunda pregunta, la respuesta es que tienes que mantener la estructura relativa de tus carpetas. Es decir, si tuvieras primero.xls en c:\ y segundo.xls en c:\toni para moverlos a c:\carpeta deberías mover segundo a c:\carpeta\toni, y no a c:\carpeta directamente. Si conservas la estructura relativa no tendrás problemas.
Espero haberte ayudado, y siento el retraso... cada vez son más las preguntas.
Un saludo,
Respuesta de nilda
1
Tengo que consultarte si tienes un archivo que inicia la cadena de vínculos, de ser así, ya que cuentas con XP, es correcta tu respuesta, podrías entonces crear una macro en el archivo origen que abra todos los archivos, actualizándolos, guardarlos y cerrarlos para que no te ocupen memoria.
Hay que tener en cuenta que sería más sencilla si todos los archivos se encuentran en la misma carpeta, en XP se pueden seleccionar todos los archivos de una misma carpeta con la tecla MAY, para la macro también podrías poner la seguridad en baja para que no te pregunte por la activación de la misma.
El siguiente ejemplo abre dos libros que están vinculados con el que yo llamo de origen
********************
Workbooks.Open Filename:= _
"C:\Documents and Settings\Nilda\Mis documentos\Ejemplos y Ayudas\vinculos\tres.xls" _
, UpdateLinks:=xlUpdateLinksAlways
Workbooks.Open Filename:= _
"C:\Documents and Settings\Nilda\Mis documentos\Ejemplos y Ayudas\vinculos\dos.xls" _
, UpdateLinks:=xlUpdateLinksAlways
**************************
UpdateLinks:=xlUpdateLinksAlways hace que "no pregunte" para actualizar los vínculos, pero si lo quieres lograr manualmente es en el menú Herramientas, Opciones, ficha Modificar, desactiva la casilla de verificación Consultar al actualizar vínculos automáticos.
Con respecto a tu segunda consulta, Excel guarda el camino completo, no se porque estará pasándote esto, pero si el nombre de la otra hoja de cálculo o del otro libro contienen caracteres no alfabéticos, se deberá poner el nombre (o la ruta de acceso) entre comillas sencillas (apóstrofo)
Hay que tener en cuenta que sería más sencilla si todos los archivos se encuentran en la misma carpeta, en XP se pueden seleccionar todos los archivos de una misma carpeta con la tecla MAY, para la macro también podrías poner la seguridad en baja para que no te pregunte por la activación de la misma.
El siguiente ejemplo abre dos libros que están vinculados con el que yo llamo de origen
********************
Workbooks.Open Filename:= _
"C:\Documents and Settings\Nilda\Mis documentos\Ejemplos y Ayudas\vinculos\tres.xls" _
, UpdateLinks:=xlUpdateLinksAlways
Workbooks.Open Filename:= _
"C:\Documents and Settings\Nilda\Mis documentos\Ejemplos y Ayudas\vinculos\dos.xls" _
, UpdateLinks:=xlUpdateLinksAlways
**************************
UpdateLinks:=xlUpdateLinksAlways hace que "no pregunte" para actualizar los vínculos, pero si lo quieres lograr manualmente es en el menú Herramientas, Opciones, ficha Modificar, desactiva la casilla de verificación Consultar al actualizar vínculos automáticos.
Con respecto a tu segunda consulta, Excel guarda el camino completo, no se porque estará pasándote esto, pero si el nombre de la otra hoja de cálculo o del otro libro contienen caracteres no alfabéticos, se deberá poner el nombre (o la ruta de acceso) entre comillas sencillas (apóstrofo)
Muchas gracias Nilda!. Tu información fue fundamental para resolver el problema. Me diste una respuesta sencillla a un problema muy complicado. Gracias
Respuesta de angibeat
1
Disculpa que no te haya respondido antes.
La verdad es que trabajar con tantos archivos en excel es un infierno, porque es un programa no muy robusto, por lo que cualquier pequeño cambio puede afectar la integridad de la data. Mi primera recomendación es que prueben con algún otro software un poco más robusto (claro eso puede ser a posteriori).
Para tu problema inmediato, yo pienso que podrían crear un archivo que contenga toda la data que desean actualizar, y sea este único el que requiera modificación, de forma tal que al cambiar un valor allí, se actualice inmediatamente en el resto de los archivos, lamentablemente es al abrir el archivo cuando este te pide ser actualizado con los vínculos que contenga.
Otra opción viable es trabajar con macros, es decir, codificar la ruta de los archivos de los cuales se desea obtener información, y de esta forma también se actualizarían automáticamente los links, claro, debes tomar en cuenta que al cambiar algún archivo de lugar, debes cambiar el código.
Otra opción es mudar algunos datos a una base de datos, Access por ejemplo, para no perder comunicación con excel, puedes activar un macro que tome información de la base de datos y te actualice automáticamente, el mantenimiento en este caso lo harías en la data que esta en la base de datos.
Bueno German, espero haberte brindado una pequeña ayudita, si no es lo que esperoabas puedes volver a escribirme, me encantaría ayudarte nuevamente.
Bye
Angie
La verdad es que trabajar con tantos archivos en excel es un infierno, porque es un programa no muy robusto, por lo que cualquier pequeño cambio puede afectar la integridad de la data. Mi primera recomendación es que prueben con algún otro software un poco más robusto (claro eso puede ser a posteriori).
Para tu problema inmediato, yo pienso que podrían crear un archivo que contenga toda la data que desean actualizar, y sea este único el que requiera modificación, de forma tal que al cambiar un valor allí, se actualice inmediatamente en el resto de los archivos, lamentablemente es al abrir el archivo cuando este te pide ser actualizado con los vínculos que contenga.
Otra opción viable es trabajar con macros, es decir, codificar la ruta de los archivos de los cuales se desea obtener información, y de esta forma también se actualizarían automáticamente los links, claro, debes tomar en cuenta que al cambiar algún archivo de lugar, debes cambiar el código.
Otra opción es mudar algunos datos a una base de datos, Access por ejemplo, para no perder comunicación con excel, puedes activar un macro que tome información de la base de datos y te actualice automáticamente, el mantenimiento en este caso lo harías en la data que esta en la base de datos.
Bueno German, espero haberte brindado una pequeña ayudita, si no es lo que esperoabas puedes volver a escribirme, me encantaría ayudarte nuevamente.
Bye
Angie
Respuesta de pduran24
1
Decir que manejar tantos libros vinculados entre sí no es nada sencillo, ni para el usuario ni para el ordenador. Pero vamos al grano, la solución más simple, es como usted indica abrirlos todos ya que un objeto vinculado se actualiza automáticamente cada vez que se abre el archivo de destino o cada vez que cambia el archivo de origen mientras se abre el archivo de destino. Al abrir el libro, aparece el mensaje de inicio preguntando si desea actualizar los vínculos. Éste es el método principal para actualizar los vínculos. Puede controlar la pregunta inicial para actualizar vínculos automáticamente sin ver la pregunta siempre.
Esta opción afecta a todos los libros abiertos.
En el menú Herramientas, haga clic en Opciones y elija la ficha Modificar.
Desactive la casilla de verificación Consultar al actualizar vínculos automáticos. Si la casilla está desactivada, los vínculos se actualizan automáticamente y no se realiza ninguna pregunta. En cuanto a la dirección de los vínculos conviene que todos los libros estén guardados en una misma carpeta (directorio) de esta forma se puede substituir el origen de todos los vínculos de una sola vez. Haciendo un reemplezo de la dirección "no válida" por la nueva, por ejemplo: buscar: "C:\Mis Documentos\" y reemplazar por "C:\ProyectoExcel\"
Esta opción afecta a todos los libros abiertos.
En el menú Herramientas, haga clic en Opciones y elija la ficha Modificar.
Desactive la casilla de verificación Consultar al actualizar vínculos automáticos. Si la casilla está desactivada, los vínculos se actualizan automáticamente y no se realiza ninguna pregunta. En cuanto a la dirección de los vínculos conviene que todos los libros estén guardados en una misma carpeta (directorio) de esta forma se puede substituir el origen de todos los vínculos de una sola vez. Haciendo un reemplezo de la dirección "no válida" por la nueva, por ejemplo: buscar: "C:\Mis Documentos\" y reemplazar por "C:\ProyectoExcel\"