Re-Linking Pivot tables to MS Query

[johnb]

Advice needed: I have 2 MS Access db’s which are the data source for 2 Excel
workbooks. These workbooks contain about 30 pivot tables in each book. Each
pivot table is linked via MSQuery to an Access query. These pivot table links
are hard coded behind the scenes. How can I use VBA to re-link these PT’s to
MS Query.

TIA
Johnb

 

[Tom Ogilvy]

Look at the connection property of the the PivotCache Object. Look at what
the current value is then make the changes to that string and reassign it.
There is also a hidden SQL property that you might want to look at.

 

[johnb]

Hi Tom,

Thank you for the comment. The hidden SQL property, is it found under
..CommandType = xlCmdSql ??

johnb