El curioso caso de las tablas dinámicas que no se actualizan.

A las disculpas presentadas en el postanterior corresponde también alguna explicación. En los últimos meses he estado desarrollando modelos con las (no tan) nuevas herramientas de Excel, Power Query y PowerPivot, lo cual consumió el poco tiempo libre que puedo dedicarle al blog.

Para quien haya trabajado exclusivamente con Excel el aprendizaje de estas nuevas herramientas presupone un esfuerzo adicional: dejar de “pensar Excel”. Además cada una de estas aplicaciones viene con sus propios lenguajes (“M” para Power Query; “DAX” para PowerPivot) y sus propias funciones. Pero, el tema de este post no es “Introducción a Power Query…” o “Como PowerPivot cambió mi vida”, sino compartir un aprendizaje relacionado con Power Query.

Hace unos días finalicé el desarrollo de un modelo para controlar lotes en una empresa farmacéutica. La herramienta ideal era PowerPivot pero por motivos que no detallaré aquí, decidí hacer todo el desarrollo con Power Query. El modelo recibe datos, parte de ellos en forma manual; consultas en Power Query realizan las transformaciones necesarias, combinan y unen datos de distintas consultas y crean una serie de tablas planas que alimentan varios reportes creados con tablas dinámicas.

Mi intención era que el usuario apretará el botón “Actualizar todo” para asegurar que los reportes reflejaran siempre los últimos datos. Pero prudentemente desconfiando de mis usuarios, decidí incluir un evento que disparara una rutina ThisWorkbook.RefreshAll cada vez que el usuario accediera a la hoja de los reportes. Para mi desconcierto, si bien las tablas de datos se actualizaban, no así las tablas dinámicas.

Ahorrándoles la vía crucis de descubrir dónde estaba el problema, voy directamente al grano. El problema pasa por la actualización en segundo plano de las consultas (background refresh), que por definición está habilitado para toda conexión que creamos

Para permitir que todas las consultas se actualicen antes que las tablas dinámicas debemos deshabilitar la actualización en segundo plano. Una posibilidad es hacerlo manualmente, pero más eficiente es hacerlo con esta macro y al mismo tiempo actualizar todas las consultas y las tablas dinámicas

 Sub Refresh_All()  
   Dim iConnectionsCount As Integer, iX As Integer  
   iConnectionsCount = ThisWorkbook.Connections.Count  
   For iX = 1 To iConnectionsCount  
     ThisWorkbook.Connections(iX).OLEDBConnection.BackgroundQuery = False  
   Next iX  
   ActiveWorkbook.RefreshAll  
   For iX = 1 To iConnectionsCount  
     ThisWorkbook.Connections(iX).OLEDBConnection.BackgroundQuery = True  
   Next iX  
 End Sub  

La primer parte de la macro deshabilita la actualización en segundo plano de todas las conexiones. Luego realiza la actualización (Refresh.All) y finalmente rehabilita la actualización en segunda plano.