Sub ListSourceData()
    'Declare our variables.
    Dim newSheet As Worksheet, sdArray As Variant
    Dim oldSheet As Worksheet, pt As PivotTable, r As Integer

    'Set our variables.
    Set oldSheet = ActiveSheet
    Set newSheet = ActiveWorkbook.Worksheets.Add

    newSheet.Range("A1").Value = oldSheet.Name
    r =  3 
    
    'Loop through each pivottable on the active sheet and place its ODBC information on a new sheet.
    For Each pt In oldSheet.PivotTables
        newSheet.Cells(r,  1 ).Value = pt.Name
        newSheet.Cells(r +  1 ,  1 ).Value = pt.PivotCache.Connection
        newSheet.Cells(r +  2 ,  1 ).Value = pt.PivotCache.Sql
        r = r +  4 
    Next pt
    newSheet.Cells.EntireColumn.ColumnWidth =  100 
    newSheet.Cells.EntireRow.AutoFit
    
End Sub

Sub ChangeServer()
    'Declare our variables.
    Dim ptc As PivotCache, oldSrv As String, newSrv As String

    'Request the name of the old server/file name.
    oldSrv = InputBox("Input the name of the old server or file path as listed in the Pivot Tables SQL string.")

    'Request the name of the new server/file name.
    newSrv = InputBox("Input the name of the new server or file path which you want the Pivot Table to point to.")

    'Replace the ODBC information of whatever PivotTable is currently active.
    Set ptc = ActiveCell.PivotTable.PivotCache
    ptc.Connection = Application.Substitute(ptc.Connection, oldSrv, newSrv)
    ptc.Connection = "ODBC;DSN=Concorde;Description=Concorde;UID=Reports;PWD=Reports"
    ptc.Sql = Application.Substitute(ptc.Sql, oldSrv, newSrv)
End Sub