Changing link to textfile in Pivottable

[Frank M.]

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.

 

[Frank M.]

I've found the solution. You make a VBA macro that
accesses all the PivotCaches of the Workbook and change
the Connection property of each PivotCache.

The connection property contains what looks like an ADO
connection string. Now, since one is working with
textfiles, there will be a reference to the text driver
and the path. One just has to change that path to the new
directory.

However, one has to remember also to change the directory
for the chosen text driver in the Datasources (Windows
XP, Controlpanel, Administration, Datasources) so that it
points to the new directory (i.e. where your textfiles
are) - otherwise it does not work.


Regards,

Frank