In Excel 2016, I can create a connection to an Oracle database (via ODBC) using Microsoft Query or the Connection Wizard.
Creating a connection using one of those methods works as expected. The external data is brought into a sheet as a table. And a connection is created.
Next, I want to reuse the connection when creating PivotTables.
Click a cell within the Excel table and insert a PivotTable based on that named table/range.
That technically works; the PivotTable directly references the Excel table/range.
But there seems to be a quirk: In order to refresh the data in the PivotTable, I need to hit
Refresh All twice. If I only hit
Refresh All once, then only the table gets refreshed, not the PivotTable.
That quirk is confusing for users. The table and PivotTable become out-of-sync if
Refresh All is only clicked once. So I'm exploring alternative options.
I want to try to reuse the connection when creating a new PivotTable from scratch. Instead of using option #1
(clicking a cell within the table and inserting a PivotTable based on that named table/range), I can click outside of the table, and insert a PivotTable to create a PivotTable by manually selecting the existing connection.
When I do that, it technically works.
But when I look at the Workbook's list of connections, I see that Excel has actually made a copy of the connection, rather than simply use the existing connection.
That's not what I want; I want to reuse the connection to avoid creating multiple redundant connections. I have multiple PivotTables I want to make, and they would all reference the same underlying Oracle data (the table references the same data too). So I have no reason to make multiple identical connections.
Is there a way to reuse an existing external datasource connection for a PivotTable? (without creating a duplicate connection)