domingo, 20 de mayo de 2012

Mantener siempre actualizada una Tabla Dinámica en Excel


 (Artículo actualizado el 29/09/2015)

Todos los usuarios que utilicen Tablas Dinámicas, saben que estas deben ser actualizadas para que muestren los posibles cambios que hubiere en los datos de origen.



De forma básica, esta actualización se puede hacer de dos formas:

  • Manual:  mediante el botón actualizar que vemos en la ficha Opciones (sólo disponible cuando el cursor está situado en la Tabla Dinámica).
  • Automática: activando la casilla "Actualizar al abrir el archivo" que hay en la pestaña "Datos" del cuadro de diálogo "Opciones" que podemos activar desde la ficha "Opciones" (como hemos dicho antes, sólo disponible cuando el cursor está situado en la Tabla Dinámica".



Vamos, en este artículo, a proponer una tercera alternativa de actualización (automática) mediante una pequeña rutina de código que debemos introducir en el evento "open" de la hoja donde está alojada la Tabla Dinámica.

¿Cómo se hace esto?

Paso 1
  • Presionamos Alt + F11, o bien en la Ficha Programador, hacemos click en el botón "Visual Basic". Se abre el Editor de Visual Basic.
  • En el explorador de proyectos, seleccionamos la hoja donde está la Tabla Dinámica que queremos actualizar de forma automática:
  • Con el botón derecho, hacemos click en dicha hoja y seleccionamos "Ver código".
Paso 2

...hecho lo anterior, se nos abre una ventana a la derecha, donde debemos escribir:

Private Sub Worksheet_Activate()
ActiveSheet.PivotTables("
NombreTablaDinámica").PivotCache.Refresh
End Sub




...donde vemos que "NombreTablaDinñamica" debe ser el nombre de nuesrta Tabla Dinámica, el cual podemos previamente definir y ver en el campo "Nombre de Tabla Dinámica" que hay en la ficha "Opciones" de las Herramientas de Tablas Dinámicas (recordamos una vez más que debemos estar situados en la Tabla Dinámica para obtener esta ficha):



El efecto que hemos conseguido es que, cada vez que activamos la hoja donde hemos alojado el código, se actualiza la Tabla Dinámica.

Debemos recordar dos cuestiones:

  • Cada vez que actualizamos....¡¡se actualizan todas las Tablas Dinámicas del libro!!.
  • No es necesario que la hoja que cause la actualización sea donde está la Tabla Dinámica.
Paso 3 (actualizado 29/09/2015)

No hay que olvidar que cuando introducimos código en un archivo de Excel, debemos "Guardarlo como..." un archivo de tipo .xlsm (Habilitado para macros). Por lo tanto, debemos ir a la pestaña Archivo (si estamos en Excel 2010, 2013 o 2016) o el botón de Office (si estamos en Excel 2007) y después elegir el comando "Guardar como..." y después el tipo de archivo que hemos comentado anteriormente.




... Aceptar.


  
José Manuel Pomares Medrano


16 comentarios:

  1. muy buen tip, congratulatios!

    ResponderEliminar
  2. Lo maximo y yo matandome donde conseguir codigo para Vb6 y la solución fue configurando el excel que tenia como plantilla y luego abrirlo en el programa y luego salvarlo con otro nombre. Gracias.

    ResponderEliminar
  3. Excelente aporte.
    Doull Orellana desde Honduras.

    He estado buscado esta solución por todo el internet, y la he encontrado gracias a su ayuda y conocimientos.

    Que tenga buen dia.

    ResponderEliminar
  4. Muy buen aporte! La macro funciona de 10.

    ResponderEliminar
  5. Me funciona siempre y cuando no cierre el archivo, al abrirlo y modificar datos no se me actualizan automaticamente tengo que volver a escribir el codigo. Como puedo hacer para que abra o cierre el archivo este siempre me funcione automaticamente?
    Gracias por la ayuda

    ResponderEliminar
    Respuestas
    1. Disculpad por mi explicación. Mi error fue considerar que sabíais algo que no tenéis por qué saber... El motivo del malfuncionamiento es que el archivo debe ser guardado posteriormente a la introducción del código como un "Archivo de Excel habilitado para maros" y que tiene la extensión .xlsm (podéis ver el artículo actualizado).

      Eliminar
  6. Buenas tardes. A mi me sucede lo mismo que Marisa Cruz, si cierro el archivo no se actuliza la tabla cuando hago cambios en los datos.

    ResponderEliminar
    Respuestas
    1. Disculpad por mi explicación. Mi error fue considerar que sabíais algo que no tenéis por qué saber... El motivo del malfuncionamiento es que el archivo debe ser guardado posteriormente a la introducción del código como un "Archivo de Excel habilitado para maros" y que tiene la extensión .xlsm (podéis ver el artículo actualizado).

      Eliminar
  7. Me pasa lo mismo que a Andrea y Marisa

    ResponderEliminar
    Respuestas
    1. Disculpad por mi explicación. Mi error fue considerar que sabíais algo que no tenéis por qué saber... El motivo del malfuncionamiento es que el archivo debe ser guardado posteriormente a la introducción del código como un "Archivo de Excel habilitado para maros" y que tiene la extensión .xlsm (podéis ver el artículo actualizado).

      Eliminar
  8. Hola, has indicado que "No es necesario que la hoja que cause la actualización sea donde está la Tabla Dinámica" ¿cómo puedo hacer que la tabla se actualice en automático sin entrar a la hoja que incluye el código? hasta ahora me veo forzado a dar clic sobre la hoja contenedora para que los datos se actualicen. ¿Existe forma de llamar esta función a través de un botón puesto en una celda?

    Gracias por tu ayuda

    ResponderEliminar
    Respuestas
    1. Por supuesto. Hay varias formas, según necesidades:

      Si generas un botón (ya sea con una figura o un botón de formulario desde la ficha Programador) puedes agregarle una macro que contenga ese mismo código (puesto en un módulo estandar) con el botón derecho del ratón y eligiendo "Asignar macro...".
      También puedes hacer que la TD se actualice mediante ese código si lo pones en un evento que se produce cada vez que se activa CUALQUIER HOJA del libro. Este evento del que te hablo pertenece a ThisWorkbook y se llama SheetActivate (por lo tanto,e l código debes ponerlo dentro de él).
      Hay todavía más formas, pero espero que alguna de estas te sirva.
      Espero haberte ayudado. Saludos.

      Eliminar
  9. Muchas gracias!!!! Excelente!!!

    ResponderEliminar