data source question

[ituser2008]

I have a pivot table created by a former colleage and it was connected to a different sql server. How do I change the datasource so it can connect to the new sql server?? I tried to create a new data source but I still can't get the pivot table to change it to the new server. Any help will be greatly appreciated.

thanks

 

[SkipVought]

I can show you haw to find the current connection and SQL, but I have not been able to CHANGE the values as advertised in VBA Help.

Run this code in a module. Start on the sheet containing the PivotTable. Values will be in SQL_Source sheet in A1 & A2...

Sub FindSource()

    Dim sd, i, wsNew As Worksheet, wsOLD As Worksheet
    Set wsOLD = ActiveSheet
    Set wsNew = Sheets.Add
    wsNew.Name = "SQL_Source"
    sd = wsOLD.PivotTables(1).SourceData
        For i = LBound(sd) To UBound(sd)
            wsNew.Cells(i, "A") = sd(i)
        Next
End Sub

Skip,

_{I'll be dressed to the nines this week, as I go to have my prostatectomy...
Because, if I'm gonna BE impotent, I want to LOOK impotent!}