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?

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
'==========================
--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html


"bdub" wrote:

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?

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

bdub

"Debra Dalgleish" wrote:

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
'==========================
--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html


"bdub" wrote:

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?

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.

--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html


"bdub" wrote:

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

bdub

"Debra Dalgleish" wrote:

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
'==========================
--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html


"bdub" wrote:

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?

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

"Debra Dalgleish" wrote:

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.

--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html


"bdub" wrote:

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

bdub

"Debra Dalgleish" wrote:

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
'==========================
--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html


"bdub" wrote:

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?