viernes, septiembre 23, 2011

Actualización automática de tablas dinámicas

Ha pasado casi un mes desde que publiqué mi última nota. Antes que mis lectores (y Google!) piensen que he abandonado el blog, publico una nota breve sobre actualización automática de tablas dinámicas en Excel.

Excel no tiene un método incorporado para actualizar tablas dinámicas en forma automática. Cambios en la base de datos se reflejarán en el reporte dinámico sólo cuando apretemos el icono Actualizar (o Alt+F5) o Actualizar Todo (Ctrl+Alt+F5) si queremos actualizar todas las tablas dinámicas del cuaderno



Para evitar tener que actualizar manualmente las tablas dinámicas de nuestro cuaderno (pensemos en un dashboard donde hacemos uso intensivo de tablas dinámicas) podemos crear un evento. El código es muy sencillo

Private Sub Worksheet_Deactivate()
    ActiveWorkbook.RefreshAll
End Sub


En este caso usamos el evento Worksheet_Deactivate de la hoja “datos” (la que contiene la base de datos del reporte dinámico). De esta manera nos aseguramos que cada actualizamos la base de datos, al pasar a otra hoja las tablas dinámicas se actualizan.

Esta técnica supone que la base de datos se encuentra en una hoja del cuaderno que contiene los reportes dinámicos. En el caso en que las tablas dinámicas se basen en fuentes de datos externas, usamos el mismo código pero lo disparamos usando el evento Worksheet_Activate de alguna de las hojas que contienen los reportes dinámicos.

20 comentarios:

  1. en mi caso lo hago al revés porque mis excel pesan mucho y no me suele interesar un cálculo general.
    Le pongo el refresh de la tablar al activar su pestaña, y de paso le meto que actualize el rango por si he metido más valores

    ResponderBorrar
  2. Excelente el código, es muy sencillo y funciona perfectamente.

    ResponderBorrar
  3. Hola Jorge,

    una pregunta, he creado la macro y metido el codigo y funciona perfectamene, lo unico es que para que actualize la tabla tengo que meterme en la macro en si y darle F5 o play (perdiendo el sentido de la macro en si) , hago algo mal?

    Graqcias por tu ayuda y por tu blog,

    Jose

    ResponderBorrar
  4. Si, parece ser que en lugar de poner el cóodigo en el módulo de la hoja que contiene la tabla, lo has puesto en un módulo común del editor de VB.
    La otra cuestión es qué evento estás usando. En la nota usamos el Deactivate, lo que hace que la tabla se actualice cada vez que salimos de la hoja. Si usáramos el evento Activate, se actualizaría cada vez que activamos la hoja.

    ResponderBorrar
  5. Hola a todos...

    encuentro el código muy útil, pero en mi caso personal (espero me puedan colaborar) necesito actualizar UNICAMENTE las tablas dinámicas, ya que en el mis libro también tengo conexiones a archivos planos, pero con el código que indican también actualiza dichas conexiones y es un problema porque no siempre se necesita actualizar dichas conexiones y si el usuario ya no tiene el archivo plano hasta ahi llego mi super hoja de excel =(.

    Entonces no se si conozcan un método para actualizar únicamente las tablas dinámicas. Gracias de antemano...

    ResponderBorrar
  6. Alejandro, para actualizar sólo una determinada tabla dinámica hay que referirse específicamente a ella en el código. Por ejemplo, supongamos que tenemos dos tablas en la hoja (TDin1 y TDin 2, siempre conviene cambiar el nombre por defecto de la tabla dinámica a algo más relevante), este código te pregunta cuál actualizar y lo hace

    Sub actualizar_Tabla_Dinamica()
    Dim strPivotName As String

    strPivotName = Application.InputBox(prompt:="Que tabla actualizar?")

    ActiveSheet.PivotTables(strPivotName).PivotCache.Refresh
    End Sub

    ResponderBorrar
  7. Alejandro, decidí ampliar y mejorar la macro. Te sugiero leer esta nota que acabo de publicar.

    ResponderBorrar
  8. Lo que hago es copiar el código tanto en la hoja donde está la tabla y gráfico dinámico como la hoja donde se encuentran los datos y funciona perfectamente! Gracias por el aporte! Peter Wolfgang Espinel

    ResponderBorrar
  9. Estimado Jorge,

    Tengo un problema de actualización, tengo varios archivos que dentro de los cuales existen diversas tablas dinamicas. He creado un macro que abre cada archivo actualiza ( ActiveWorkbook.RefreshAll), guarda y cierra. Mi duda es si podre crear una macro genrica que pueda realizar la actualizacion de las todas las tablas dinamicas de todos los archivos ubicados en un directorio.

    Muchas gracias,
    Iván

    ResponderBorrar
  10. Iván, creo que si. Pero me parece que tendrías que repensar como están organizados tus datos. Tal vez sería má conveniente consolidar los datos en un "flat file". Fijate en las notas sobre Power Query que he publicado ultimamente.

    ResponderBorrar
  11. Hola Jorge,

    Mi problema es este:

    Quiero actualizar automaticamente una tabla con el valor que indico en otra con esta función:

    Private Sub Worksheet_Calculate()

    Dim sexo As String

    sexo = Range("c7").Value

    Range("C22").FormulaR1C1 = sexo

    End Sub

    Todo va bien hasta que selecciono (Todas). Ahí me dice que (Todas) no pertenece a los valores de la tabla, Sin embargo si lo hago en inglés si funciona con (All).

    ¿Por qué puede ser?

    Un saludo

    ResponderBorrar
  12. Sucede que Vba no habla castellano, sólo gringo. Lo que no entiendo es el código del evento Calculate (no es una función, sino un evento).

    ResponderBorrar
  13. Hola estoy confeccionando un tipo de Comprobante de Salario, en el encabezado viene la informacion basica del empleado, hice una lista para que en este documento se seleccione solo el codigo del empleado y se complete la informacion correspondiente halando la info que esta en una base de datos, esta parte no es una tabla dinamica, pero la informacio salarial si es la tabla dinamica pero no logro que a la hora de cambiar de empleado (es decir de ID del empleado), me hale automaticamente la informacion salarial, si no que tengo que presionar la opcion acutalizar todo, para que lo haga, se podra hacer automatico?

    ResponderBorrar
  14. Tendrías que programar un evento (si no estás familiarizado con los eventos puedes hacer una búsqueda en mi blog con la palabra "evento").
    Los eventos son una espeia de macro ligada a lo que pasa en una hoja específica. En tu caso el evento tendría que disparar el código que actualiza la tabla dinámica cuando ocurre un cambio en la celda que contiene el ID del empleado.
    En tu caso tendrías que usar el evento Worksheet_Change. Supongamos que la celda que contiene el ID del empleado es B5, el código sería

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Union(Target, Range("B5")).Address = Range("B5").Address Then
    ThisWorkbook.RefreshAll
    End If
    End Sub

    para actualizar todas las tablas dinámicas del cuaderno. Si quieres acctualilzar una tablla específica tendrías que usar

    ActiveSheet.PivotTables(el nombre de la tabla).PivotCache.Refresh

    en lugar de ThisWorkbook.RefreshAll

    ResponderBorrar
  15. BUEN DÍA!

    TENGO UNA BASE DE DATOS EN LA HOJA 1 Y EN LAS DEMÁS HOJAS SOLO TABLAS DINÁMICAS, EN ESTA BASE CONSTANTEMENTE AGREGO MAS DATOS Y NECESITO QUE SE VEAN REFLEJADOS EN LAS TABLAS DINÁMICAS AUTOMÁTICAMENTE. INTRODUJE EL CÓDIGO (EN LA HOJA DE LA BASE DE DATOS) PERO AUN ASÍ NO ME ACTUALIZA LAS TABLAS DINÁMICAS CON LOS DATOS AGREGADOS. QUE ESTOY HACIENDO MAL? SERIAS TAN AMABLE DE AYUDARME?? GRACIAS

    SALUDOS!!!!

    ResponderBorrar
  16. Sin ver tu cuaderno no puedo decirte (fijate en el enlace Ayuda, en la parte superior del blog).

    ResponderBorrar
  17. como actualizar una tabla dinamica cuando otra se tabla dinamica cambie

    ResponderBorrar
  18. La actualización de una tabla dinámica no implica la actualización de otras, a menos que estén basadas en los mismos datos. Necesitaría que expliques un poco más el contexto de lo que quieres hacer.
    De todas maneras, podrías usar el evento Worksheet_PivotTableUpdate refiriéndose a la pivot 1 para disparar la actualización de la pivot 2.

    ResponderBorrar
  19. Hola tengo una tabla dinamica que se alimenta de una tabla de sql, mis usuarios no tienen permiso para actaulizarla entonces tengo que hacerlo todos los dias manualmente. QUe me recomiendan hay algo que pueda actaulizar el excel automaticamente sin que yo lo tenga que abrir?

    ResponderBorrar
  20. No creo que haya ninguna forma de actualizarla sin abrir el cuaderno.

    ResponderBorrar

Nota: sólo los miembros de este blog pueden publicar comentarios.