External Connection Greyed Out

[jfearn]

I have a pivot table bound to a data range in a separate excel worksheet. Now I want to bind the table to an external source (MS Access2010). If i choose "Change Data Source" from pivottable ribbon, I can only choose another range, but external connection is greyed out.
Is there a way to do this, otherwise I'd have a lot of work recreating many pivottables.?
Thanks

[Olly]

I'm not sure there's an easy way to do it...

You could maybe create a new pivottable from your external connection, then use a bit of code to change the pivotcache for all existing pivot tables:

Sub ChangePivotCaches()
    Dim ws As Worksheet
    Dim pt As PivotTable
    Dim ptX As PivotTable
    
    'Change this reference to the "new" pivot table based on the external connection
    Set ptX = Worksheets("Sheet1").PivotTables("ptExternal")
    
    For Each ws In ThisWorkbook.Worksheets
        For Each pt In ws.PivotTables
            If Not pt = ptX Then pt.CacheIndex = ptX.CacheIndex
        Next pt
    Next ws
    
End Sub