Dynamically change the database a PivotTable references

[daxmiller]

Can anyone tell me if there is a simple way to do the following ... I
have a bunch of PivotTables in workbook (Excel 2003) and from time to
time I want to move the underlying Access database (in which the
source data resides) to a different folder on the network. I
effectively want all my PivotTables to point to this new location.
Right now I can't do this without rebuilding all my PivotTables. I am
sure all of this can be done programatically using VBA but I was
wondering whether there is some more simple method e.g. through
changing the ODBC link reference. Does anyone have a simple solution
to this?

 

[Roger Govier]

Hi

Ron Coderre has written a very nice addin for changing data sources for
Pivot Tables.
It can be downloaded at
http://www.contextures.com/xlPivotPlay01.html

 

[daxmiller]

Hi

Ron Coderre has written a very nice addin for changing data sources for
Pivot Tables.
It can be downloaded athttp://www.contextures.com/xlPivotPlay01.html

Thanks Roger, appreciate the help.