A colleague has a number of spreadsheets with
Pivottables. Due to maintenance they have now been moved
to a new disk. All the text files have also been moved
and the whole directory structure on the new disk is
identical to the one on the old disk.
However, when we try to recalculate the Pivot tables, the
spreadsheet still try to open the text files on the old
disk. To handle this we have updated the ODBC Connection
that uses a text driver to access the files, specifying
the path on the new disk. Still, the spreadsheet looks
for the old files.
Can the datasource easily be changed to use the new
location. I hope so, otherwise we have to open each
Pivottable and go through the process of choosing each
field again - we are talking many spreadsheets and files,
so help will much appreciated.

I am considering writing some generic VBA code that could
change the pivot object (there must be a property for the
data source). Any hints on how to handle that object?


Regards,

Frank M.