I am on Office 365 running 64 bit Excel, version 16.0.9126.2336 on a Windows 10 machine
I am trying to understand the behavior and related VBA properties for a pivot table when I select a regular range of data on a sheet, then go to Insert>PivotTable, and choose the "Add this data to the data model" option. Once I do this, from a GUI perspective the PivotTable that is created appears to have a data source of a workbook connection that gets created referencing the specific range I selected. The connection is named something like "WorksheetConnection_Sheet1!$A$1:$C$9" depending on the sheet and range selected (this is what you see if you go to the Analyze tab in the PivotTable Tools section of the ribbon and click Change Data Source>Connection Properties).
However, from a VBA perspective, looking at the PivotTable and related PivotCache objects, I can't find any reference to this specific connection or the specific range I selected. Because of the "Add to data model" option selected the data source in VBA is a connection called "ThisWorkbookDataModel" which is shown in the WorkbookConnection property for the PivotCache object in VBA. However, I don't see that from a GUI perspective related to the PivotTable, except for if if I go to the Queries and Connections pane, and right click to see the properties of the the "ThisWorkbookDataModel" connection, the pivot table is shown on the "Used In" tab for that connection (it is also shown this way for the other workbook/range connection).
So, it appears that creating a PivotTable this way creates two different connections, and the PivotTable is somehow using both of them? In VBA all I can really find reference to is the data model connection. How can I get information related to the the workbook/range connection that also got created?
I am trying to understand the behavior and related VBA properties for a pivot table when I select a regular range of data on a sheet, then go to Insert>PivotTable, and choose the "Add this data to the data model" option. Once I do this, from a GUI perspective the PivotTable that is created appears to have a data source of a workbook connection that gets created referencing the specific range I selected. The connection is named something like "WorksheetConnection_Sheet1!$A$1:$C$9" depending on the sheet and range selected (this is what you see if you go to the Analyze tab in the PivotTable Tools section of the ribbon and click Change Data Source>Connection Properties).
However, from a VBA perspective, looking at the PivotTable and related PivotCache objects, I can't find any reference to this specific connection or the specific range I selected. Because of the "Add to data model" option selected the data source in VBA is a connection called "ThisWorkbookDataModel" which is shown in the WorkbookConnection property for the PivotCache object in VBA. However, I don't see that from a GUI perspective related to the PivotTable, except for if if I go to the Queries and Connections pane, and right click to see the properties of the the "ThisWorkbookDataModel" connection, the pivot table is shown on the "Used In" tab for that connection (it is also shown this way for the other workbook/range connection).
So, it appears that creating a PivotTable this way creates two different connections, and the PivotTable is somehow using both of them? In VBA all I can really find reference to is the data model connection. How can I get information related to the the workbook/range connection that also got created?
