todas - modificar tabla dinamica con vba

Excel VBA-RefreshAll no actualiza las tablas dinámicas

Question

Tengo un libro de trabajo de la plantilla, que tiene varias tablas de datos conectadas a conexiones sql, así como algunas tablas dinámicas cuyas fuentes son los datos aportados.

Tenía la impresión de que ActiveWorkbook.RefreshAll actualizaría todas las conexiones y luego actualizaría los pivots. Eso, de hecho, es lo que sucede cuando ejecuto la actualización de forma manual. Sin embargo, cuando ejecuto el VBA (que en realidad está en Access, pero está referenciado correctamente, etc.) actualiza las conexiones, pero NO las tablas dinámicas.

He probado DoEvents después de RefreshAll que no tuvo ningún efecto.

¿Es mi única opción ahora ejecutar un For each en todas las hojas de trabajo, orígenes de datos, cachés dinámicos y actualizarlos de esa manera?

Answer 1

ActiveWorkbook.RefreshAll hace como de hecho RefreshAll connections and pivots. Sin embargo, en su escenario, los pivotes probablemente se basan en los datos que debe actualizar primero. El pivote se actualizará mientras los datos no se cargan aún, de ahí el comportamiento inesperado.

Hay múltiples soluciones para esto:

• O bien, se han devuelto los datos a través de la conexión como un pivote, por lo que la tabla dinámica se actualizará automáticamente cuando se devuelvan los datos. De esta forma, tampoco tendrá los datos almacenados en una hoja separada en su libro de trabajo.

• Establezca la propiedad "Actualizar en fondo" en falso para todas las conexiones, ya sea en el código o a través de la interfaz de usuario, y luego ejecútelo como normalmente. Dos veces La segunda vez los pivotcaches tendrán los datos actualizados y, por lo tanto, actualizarán como se esperaba. - Editar: no lo recomiendo, ya que abrirá la conexión db dos veces, cargará los datos dos veces, etc. ¡Muy ineficiente!

• Establezca la propiedad "Actualizar en segundo plano" a false como se mencionó anteriormente. Después de actualizar con Actualizar todo, recorra la colección pivottable de sus hojas de trabajo para actualizarlas manualmente después de que los datos se hayan cargado como se muestra a continuación.

Código:

Sub test()
Dim ws as Worksheet
Dim pt as PivotTable
ActiveWorkbook.RefreshAll ''make sure the refresh in bg property is false for all connections
For each ws in ActiveWorkbook.Worksheets
For each pt in ws.pivottables
pt.RefreshTable
Next pt
Next ws
End Sub

O simplemente actualice los pivotcaches (más eficiente, especialmente si varias tablas usan el mismo caché):

Sub test()
Dim pc as PivotCache
ActiveWorkbook.RefreshAll ''make sure the refresh in bg property is false for all connections
For each pc in ActiveWorkbook.PivotCaches
pc.Refresh
Next pc
End Sub

Answer 2

He resuelto el problema agregando un simple ''Calcular'' al código.

Calculate
ActiveWorkbook.RefreshAll

Answer 3

He resuelto el problema usando el siguiente

 For Each sht In .Sheets
For Each qt In sht.QueryTables
qt.Refresh
Next qt
For Each lo In sht.ListObjects
lo.QueryTable.Refresh BackgroundQuery:=False
Next lo
For Each pvt In sht.PivotTables
pvt.PivotCache.Refresh
Next pvt
Next sht