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