Update table dynamically last. VBA

Question

Update table dynamically last. VBA

Navigation

#1 by (0 votes)

-1

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

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

In the case I need the pivot tables to be last updated, because the data is interlinked.

I tried this macro but without success:

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 this?

excel vba

asked by anonymous 13.02.2017 / 12:10

1 answer

Wrong encoding in MySQL when I enter through AJAX How to create graphics in Genexus X?

score 0 · Answer 1

According to this answer , we see that a possible solution to your problem might be as follows:

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

Withtheoption"Habilitar Atualização em Segundo Plano" disabled , your macro should wait until your external data is updated before jumping to 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 background refresh option with 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 this answer , the user said that they updated the above code to disable the background update of all connections existing 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