Actualización automática de tablas dinámicas

viernes, septiembre 23, 2011

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 comments:

Diego,  04 octubre, 2011 16:41  

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

Anónimo,  25 junio, 2012 09:40  

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

Anónimo,  17 agosto, 2012 14:35  

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

Jorge L. Dunkelman 17 agosto, 2012 19:34  

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.

Andrés Alejandro Marin Morales 10 enero, 2013 22:28  

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

Jorge L. Dunkelman 11 enero, 2013 13:19  

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

Jorge L. Dunkelman 11 enero, 2013 17:41  

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

Peter Wolfgang Espinel 31 julio, 2013 18:52  

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

Unknown 10 febrero, 2014 18:38  

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

Jorge Dunkelman 11 febrero, 2014 12:13  

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.

Super69 25 marzo, 2014 16:41  

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

Jorge Dunkelman 25 marzo, 2014 17:50  

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

Anónimo,  05 octubre, 2014 03:35  

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?

Jorge Dunkelman 05 octubre, 2014 18:48  

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

Unknown 25 mayo, 2016 00:58  

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

Jorge Dunkelman 30 mayo, 2016 07:27  

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

Cesia Lecca 08 febrero, 2017 19:08  

como actualizar una tabla dinamica cuando otra se tabla dinamica cambie

Jorge Dunkelman 09 febrero, 2017 18:35  

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.

karla 06 julio, 2017 22:12  

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?

Jorge Dunkelman 07 julio, 2017 10:38  

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

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP