miércoles, 20 de agosto de 2014

Excel: refresco automático de origen de datos en tablas dinámicas

Dada la estrecha relación de Excel con los procesos de B.I. y de análisis de información, vamos a iniciar una serie de posts de Excel, en el que veremos diferentes utilidades de aplicación al análisis de datos.



Es habitual en un libro Excel en el que estamos analizando una tabla de datos, el disponer diferentes tablas dinámicas que apuntan a la misma tabla fuente de datos, pero muestran la información desde una diferente perspectiva camibiando el nivel de detalle, los ejes de análisis, los filtros o realizando zoom específicos sobre determinados datos. De forma, que podemos tener un conjunto de tablas dinámicas estructuradas en diferentes hojas, pero apuntando al mismo origen de datos: hoja de detalle, fichero externo, tabla access o tabla sql server externa, etc..

Un cambio de estructura o simplemente de nombre en nuestro origen de datos nos obligaría a volver a vincular todas las tablas dinámicas apuntando al nuevo origen de datos. Podemos automatizar este proceso realizando el vínculo solo para una taba dinámica que utilizaremos como tabla de referencia, para después actualizar el resto mediante una macro que recorra todas las tablas dinámicas actualizando el origen de datos en base al origen vinculado a la tabla dinámica de referencia.

El código de la macro sería el siguiente:

Sub Vincula_Tablas()

Dim pt As PivotTable
Dim ws As Worksheet

 ' seleccionamos la tabla dinámica de referencia, situada en la hoja: "hoja de    referencia", si hubiera
 ' varias tablas en esta hoja, deberíamos seleccionar la que corresponda
Set ptMaes = Worksheets("HOJA REFERENCIA").PivotTables(1)
 'actualizamos la configuración al resto de tablas dinámicas de el libro de trabajo
For Each ws In ActiveWorkbook.Worksheets ' recorre todas las hojas
  For Each pt In ws.PivotTables ' recorre todas las tablas dinámicas de la hoja
    pt.CacheIndex = ptMaes.CacheIndex ' actualiza origen datos
  Next pt  ' fin bucle tablas dinámicas
Next ws ' fin bucle hojas 

End Sub

De igual forma, si tuviéramos que actualizar todas las tablas dinámicas, debido a un cambio en los datos de las fuentes origen, podemos hacerlo de modo similar:


Sub Actualiza_Tablas()

Dim pt As PivotTable
Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets ' recorre todas las      hojas
  For Each pt In ws.PivotTables ' recorre todas las tablas dinámicas de la hoja
    pt.PivotCache.Refresh  ' actualiza tabla dinámica
  Next pt  ' fin bucle tablas dinámicas
Next ws ' fin bucle hojas 

End Sub



2 comentarios:

  1. Muchas Gracias! ha sido muy útil y sencillo.
    Me permito hacerte una pregunta. Si en le mismo libro tenemos tablas con 2 fuentes distintas ¿se podría hacer lo mismo?¿que se debería modificar de la macro?

    ResponderEliminar
  2. Si, puedes ver ejemplos en www.excelyvba.com

    ResponderEliminar