Excel 2007 change pivot data source

[Guest]

I have a pivot attached to a list on a tab in the same workbook. When I
click on the Change Data Source option, the "Use an External Data Source"
button is greyed out (I have a connection created already which I could
select).

How can I change the pivot from a local to an external data source?

 

[Guest]

You could create a temporary pivot table based on the connection to the
database. Then, programmatically set all the existing pivot tables to use the
same pivot cache.
For example, with the temporary pivot table on a worksheet named Temp:

'====================
Sub ChangeCache()
Dim ptNew As PivotTable
Dim ws As Worksheet
Dim pt As PivotTable

Set ptNew = Worksheets("Temp").PivotTables(1)
Debug.Print ptNew.CacheIndex

For Each ws In ActiveWorkbook.Worksheets
For Each pt In ws.PivotTables
pt.CacheIndex = ptNew.CacheIndex
Next pt
Next ws

End Sub
'==========================

 

[Guest]

This code did it, thanks (tho', there really should be a way to do this
manually).

bdub

 

[Guest]

I haven't found an efficient way to do it manually. You can open the old
PivotTable Wizard (Alt+D, p) and go back to Step 1, and change to an external
data source. Then select the database that you want to use.

 

[Guest]

I've tried this several times - and each time Excel sends an error report and
shuts down. ugh...