ODBC data source updates after Pivot refreshes

Question

I am trying to create an Excel macro, that refreshes an ODBC data connection and then refreshes all the linked Pivot tables afterwards.

I am using the below code, I have also tried various different interations, but no matter what I do the Pivot tables refresh first and then the data refresh happens afterwards, meaning the pivots show the old data.

Sub AutoUpdate()

        For Each objConnection In ThisWorkbook.Connections
        objConnection.Refresh
        DoEvents

        Next
            Dim Sheet As Worksheet, Pivot As PivotTable
            For Each Sheet In ThisWorkbook.Worksheets

                For Each Pivot In Sheet.PivotTables
                Pivot.RefreshTable
                Pivot.Update
                Next

            Next

' ODBC Data Refresh starts here!!

End Sub

How can I get the ODBC data to refresh before the pivots?

I have also tried to use a DoEvents command, but that doesn't seem to make any difference.

Any help greatly appreciated.

Rory Rory · Accepted Answer · 2015-01-13T11:38:41

If they are all ODBC connections:

For Each objConnection In ThisWorkbook.Connections
objConnection.ODBCConnection.BackgroundQuery = False
objConnection.Refresh
DoEvents

Next

ODBC data source updates after Pivot refreshes

1 Answers