Refreshing Pivot requires opening source workbook

Question

I wrote the following sub for a workbook containing 12 pivot tables (PT) in 6 sheets, all pointing to the same external source (another workbook). It works fine, except that if I wish to manually Refresh one pivot, Excel tells me I need to open the source file. If I then open the pivot's source file, the refresh works OK.
I would prefer to have the pivot cache refreshed in the background, like it once was. What am I missing ?

Sub ChangePivotSourceData(src As String)

    Dim pt As PivotTable, wks As Worksheet, pc As PivotCache

    'update #1 pt in PIVOT AF
    Sheet2.PivotTables(1).ChangePivotCache ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=src)

    'adjust all others
    For Each wks In ThisWorkbook.Worksheets
        Debug.Print Now, wks.Name
        For Each pt In wks.PivotTables
            Debug.Print Now, pt.Name
            pt.CacheIndex = Sheet2.PivotTables(1).CacheIndex
            pt.RefreshTable
        Next pt
    Next wks

    Debug.Print Now, "ChangePivotSourceData complete"
End Sub

The sub is called like this:

Workbooks.Open fn, False, True
shMenu.Range("c5").Value = fn

Application.Calculation = xlCalculationManual
'change source of ALL pivots
ChangePivotSourceData fn & "!feesIn"     'feesIn is a named range

Why not build the PTs off a PQ connection instead? Refreshing the queries will subsequently refresh the PTs. — Dean
@DeanDeVilliers PQ means Power Query ? I am trying to accelerate a existing manual process. Not sure Power Query is installed on client machines (Excel 2013). And users are...well...accountants :-) — Patrick Honorez

Patrick Honorez Patrick Honorez · Accepted Answer · 2019-12-11T15:34:24

Took me a few hours but I found a solution.
The problem is that - at first sight - you can't create a connection to an Excel range. You must have a whole sheet, or a table. In my case, the source data starts at B28 and could not be converted into a table. So I manually created the connection pointing to the whole sheet, bringing back of few rows of garbage. I then went the data/properties/definition, and
- I changed the Command type from Default to SQL
- I changed the Command text to a SQL statement like this: select * from [mySheet$b28:bt4000] where ccy is not null (note the required $ at end of sheet name)

Then I managed to get all pivots to point to that connection and finally I wrote a function to allow changing the connection source workbook name:

With ThisWorkbook.PivotCaches(1)
    adoConn = Split(.Connection, ";")
    adoConn(3) = "Data Source=" & fn
    .Connection = Join(adoConn, ";")
    .Refresh
    'Debug.Print Now, .CommandText   'sql statement
End With

This way the refreshes are fast, and indeed I only need to refresh the connection to have all 12 PT updated.

Refreshing Pivot requires opening source workbook

1 Answers