Excel crosstabs, pivot tables FAQ and best practices

1.2. Modify source SQL statement of Excel Pivot Table

To modify MS Excel crosstab source SQL statement, use the following script:
Note: Use the immediate window in VBA editor to track output (Ctrl+G)

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

    Dim pc As PivotCache
    Dim oldSql As String
    Dim newSql As String

    newSql = "SELECT * FROM NEW_FACT_TABLE"
    
    For Each pc In ActiveWorkbook.PivotCaches
        
        oldSql = pc.Sql
        Debug.Print "Before: " & oldSql
        
        pc.Sql = Application.Substitute(pc.Sql, oldSql, newSql)
        
        Debug.Print "After: " & pc.Sql
   
    Next pc
End Sub