2

We have large Excel reports with LOTS of PivotTables. Is there any way to change the data source on the existing PivotTables to change them from an External connection to use the internal Data Model from PowerPivot?

When I choose PivotTable Analyze > Change Data Source > Change Data Source... enter image description here

it looks like I can change to a different external data source, but not an internal one. enter image description here

I looked at VBA solutions, and:

The ChangePivotCache method can only be used with a PivotTable that uses data stored on a worksheet as its data source. A run-time error occurs if the ChangePivotCache method is used with a PivotTable that is connected to an external data source.

I would even be happy with a VBA solution that created new pivots and slicers using the old one as a guide. All the columns and tables names are the same. There are just too many pivots to manually migrate!

TheRizza
  • 1,577
  • 1
  • 10
  • 23
  • 1
    What options do you see under "Choose Connection"? Normally the Workbook Data Model is listed under that. – Luke_0 May 30 '23 at 17:43
  • If the VBA API can't make any changes, the only way to do it might be via `SendKeys`, but that [might not be a stable solution](https://twitter.com/DecimalTurn/status/1096454137431105541). – DecimalTurn Jun 01 '23 at 03:07

1 Answers1

1

The answer is sadly NO. Because that is the restricted manner in which MS designed it. The good part is you can create a VBA that loops through all of the PivotTables in your workbook, reads their properties (such as the fields used in the rows, columns, values, and filters areas), and then creates new PivotTables using the internal Data Model as the data source and sets their properties to match the original PivotTables.

It is not possible to change the data source of an existing PivotTable from an external connection to the internal Data Model from PowerPivot. When you create a PivotTable, you must choose the data source that the PivotTable (PTbs) will use. Once the PivotTable is created, you can change the data source to a different external data source, but you cannot change it to use the internal Data Model.

A template VBA script that does this It uses internal source for its new PTbs

user1874594
  • 2,277
  • 1
  • 25
  • 49
  • I was hoping there was a crafty way to change the source in VBA, but your VBA script looks like it will help the "hard way" of recreating them to be much easier. Thanks for sharing! – TheRizza Jun 07 '23 at 02:02
  • Hopefully *MS* folks will realize their *smart design* and make changes in future – user1874594 Jun 07 '23 at 02:36