Re-Linking Pivot tables to MS Query
 

Advice needed: I have 2 MS Access dbs 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 PTs to
MS Query.

TIA
Johnb

Re-Linking Pivot tables to MS Query
 

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.

--
Regards,
Tom Ogilvy


"johnb" wrote:

Advice needed: I have 2 MS Access dbs 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 PTs to
MS Query.

TIA
Johnb

Re-Linking Pivot tables to MS Query
 

Hi Tom,

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

johnb

"Tom Ogilvy" wrote:

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.

--
Regards,
Tom Ogilvy


"johnb" wrote:

Advice needed: I have 2 MS Access dbs 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 PTs to
MS Query.

TIA
Johnb