excelms-accessvba

RefreshAll not updating pivot tables

I have a template workbook, which has several data tables connected to sql connections, as well as some pivot tables who's sources are the data brought through.

I was under the impression that ActiveWorkbook.RefreshAll would update all connections, then update the pivots. That in fact is what happens when I run the refresh all manually. However, when I run the VBA (which is actually in Access, but is correctly referenced etc) it updates the connections but NOT the pivot tables?

I've tried DoEvents after the RefreshAll which had no effect.

Is my only option now to run a For each through all the worksheets, data sources, pivot caches and refresh them that way?

Solution

  • I have solved the issue by using the following

        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