Update dynamic table last. VBA

Question

I created a spreadsheet connected to my ERP via ODBC and have some dynamic tables in the same spreadsheet.

I’m trying to create a macro that updates all the data, but using ActiveWorkbook.RefreshAll the command is updating the dynamic tables first and then the ODBC connections.

In case I need the dynamic tables to be updated last, because the data are interconnected.

I tried this macro but unsuccessfully:

Sub RefreshAll_AgingStock()

    Application.ScreenUpdating = False
    ActiveWorkbook.RefreshAll
    Application.CalculateUntilAsyncQueriesDone
    Worksheets("PivotTable2").PivotTables("PivotTable2").PivotCache.Refresh
    Application.ScreenUpdating = True

End Sub

How can I do that?

Answer 1

According to that answer, we see that a possible solution p/ your problem may be the following:

In Propriedades de Conexões, clear the option "Enable Background Refresh" or "Habilitar Atualização em Segundo Plano". This will cause the connection to be updated only when specified by the user, not in the background while other processes are running.

With the option "Habilitar Atualização em Segundo Plano" disabled, your macro should wait until your external data is up to date, before skipping p/the next line of code.

ActiveWorkbook.Connections("CONNECTION_NAME").Refresh
Sheets("SHEET_NAME").PivotTables("PIVOT_TABLE_NAME").PivotCache.Refresh

You can also turn off the update option in the background using the following command:

ActiveWorkbook.Connections("CONNECTION_NAME").ODBCConnection.BackgroundQuery = False

Implementing in your code:

Sub RefreshAll_AgingStock()

    ActiveWorkbook.Connections("CONNECTION_NAME").ODBCConnection.BackgroundQuery = False
    Application.ScreenUpdating = False
    ActiveWorkbook.RefreshAll 'or ActiveWorkbook.Connections("CONNECTION_NAME").Refresh
    Worksheets("SHEET_NAME").PivotTables("PIVOT_TABLE_NAME").PivotCache.Refresh
    Application.ScreenUpdating = True

End Sub

---

Already in that reply, user said they updated the above code to disable background update of all existing connections in the workbook, and after that, update the existing data in them.

Sub Refresh()

    Dim conn As Variant

    For Each conn In ActiveWorkbook.Connections
        conn.ODBCConnection.BackgroundQuery = False
    Next conn

    ActiveWorkbook.RefreshAll
End Sub