Excel crosstabs, pivot tables FAQ and best practices

1.1. Modify source connection parmeters of Excel Crosstab

To modify pivot table connection sources in all crosstabs in a workbook use the following code in a Visual Basic Editor in Microsoft Excel:
Note: Use the immediate window in VBA editor to track output (Ctrl+G)

' (c) www.etl-tools.info
Sub ModifyPivotSource()

    Dim pc As PivotCache
    Dim oldDBSID As String
    Dim newDBSID As String

    oldDBSID = “DWOLD”
    newDBSID = “DWNEW”
    
    For Each pc In ActiveWorkbook.PivotCaches
        
        pc.Connection = Application.Substitute_ 
	(pc.Connection, oldDBSID, newDBSID)

        Debug.Print "After: " & pc.Connection
    Next pc

End Sub