External Connection Greyed Out

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