One of my co-workers has got to update an excel report to bring in 2018 data, has written a new query to the Oracle db and now has to go through every pivot table in the report (about 300) changing the data source to the new connection. However, some pivot tables take their data from the current connection configured in the workbook and some take data from data tables saved in the workbook itself.

I need to come up with a way to check the data source for each pivot table and, if it equals the old connection name, change it to the new......or she'll still be doing this this time next week.

However, VBA help/google isn't being very helpful. The pivot table class doesn't seem to have a property called 'connection' or 'dataconnection' or anything similar, so I'm unsure how to identify what data source a pivot table is currently using. Closest I can find is: pivottable.PivotCache.Connection......but trying to read this value results in the error: "Not an ODBC data source or web query".


Anyone point me in the right direction?