Blue1971
New Member
- Joined
- May 19, 2020
- Messages
- 15
- Office Version
- 2016
- Platform
- Windows
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.
For certain PivotTables, I want to change the data source from using an external data source called
Reason:
The
If I try to change the PivotTable data source to
So, alternatively, I want to connect to an existing Excel Table (in a different sheet) that already uses
Problem:
When I try to change the PivotTable data source (Analyze > Change Data Source), the option to choose an Excel Table is disabled:
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.
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 like ListConn1
. It will re-use the existing ListConn
connection, as expected.Problem:
When I try to change the PivotTable data source (Analyze > Change Data Source), the option to choose an Excel Table is disabled:
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.