Hello,
I've inherited an Excel workbook. This workbook contains a GCP external data connection, and creates a number of copies using this data to distribute to a number of end users. The workbooks that the end users ultimately get contain 20 or so PivotTables (connected to the external data connection) that each are linked to five slicers. The end users are complaining about the slicer performance (changing slicer options takes ~10 seconds each time).
After the spreadsheets are created the data is fixed, so I'd like to bring the source data into a table in the workbook, and change the PivotCache that the PTs refer to to be based on the internal data, in the hopes that I might see a performance boost. I've tried to follow the instructions at the link here: Change PivotTable source from external data source connection to table/range but the gist is create a new PivotTable using the new internal data from the table, and change the PivotTable.CacheIndex property of the existing PT to match the new one - which works if the number of fields in both data sources are the same. I think this is where I'm running into problems - the external datasource also is referenced in some measure definitions, so I guess I need to recreate these measures using the table I've created also?
Would very much appreciate any help anyone might be able to give on this. Thanks very much in advance!
I've inherited an Excel workbook. This workbook contains a GCP external data connection, and creates a number of copies using this data to distribute to a number of end users. The workbooks that the end users ultimately get contain 20 or so PivotTables (connected to the external data connection) that each are linked to five slicers. The end users are complaining about the slicer performance (changing slicer options takes ~10 seconds each time).
After the spreadsheets are created the data is fixed, so I'd like to bring the source data into a table in the workbook, and change the PivotCache that the PTs refer to to be based on the internal data, in the hopes that I might see a performance boost. I've tried to follow the instructions at the link here: Change PivotTable source from external data source connection to table/range but the gist is create a new PivotTable using the new internal data from the table, and change the PivotTable.CacheIndex property of the existing PT to match the new one - which works if the number of fields in both data sources are the same. I think this is where I'm running into problems - the external datasource also is referenced in some measure definitions, so I guess I need to recreate these measures using the table I've created also?
Would very much appreciate any help anyone might be able to give on this. Thanks very much in advance!
