2

In Excel 2016, I have several PivotTables (and corresponding PivotCharts) that use external data sources. The data sources were created via Microsoft Query (an Excel mechanism) and have complex SQL logic that queries an Oracle database.

enter image description here

For certain PivotTables, I want to change the data source from using an external data source called ChartsConn to using an Excel Table.

Reason:

The ChartsConn external data source connection is redundant and should be removed -- so that future SQL changes in connections only need to be done in one place. I already have a connection that is exactly the same called ListConn; I want to use it instead.

If I try to change the PivotTable data source to ListConn, it doesn't work the way I want it to. Excel makes a copy of the ListConn connection called ListConn1, which isn't what I want.

So, alternatively, I want to connect to an existing Excel Table (in a different sheet) that already uses ListConn. If I connect to that Excel Table, then I believe Excel will do it without creating a new connection; it will re-use the existing ListConn connection as expected.

Problem:

When I try to change the PivotTable data source using Analyze > Change Data Source, the option to choose an Excel Table is disabled:

enter image description here

Question:

Is there a way to change a PivotTable's source from an external data source connection to an Excel Table?

If not, it seems like the only other option is to recreate my multiple PivotTables and PivotCharts from scratch. The only difference is I'll use the Excel Table as the source -- by clicking in the Excel Table and clicking Create Pivot Table.

That would work, but would take a lot of effort since there are multiple PivotTables and corresponding PivotCharts with very specific configurations.

Improve this question
2
  • Not in the UI, but the (old) answers here say you can shunt them over to an internal connection by re-targeting their PivotTable.CacheIndex with some VBA, as long as the fields match up: stackoverflow.com/q/21727564/7411885
    – Cpt.Whale
    Commented Feb 2, 2023 at 21:18
  • @User1974 ,,, one another way is ,,,, create a data set,,,, then Select it,,, next from DATA Tab,,, hit From Table Range,, Excel first convert it to Table then open Power Query,,,, from there U can use Close & Load to ,,,,, and create either a connection of PT!!
    – Rajesh Sinha
    Commented Feb 3, 2023 at 4:42

0

Reset to default

You must log in to answer this question.

Browse other questions tagged .