Changing link to textfile in Pivottable

F

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.
 
B

BrianB

Yes, you do have problems. Try recording a macro as you do the job. Yo
will need to open the source file as well, otherwise you get a
"invalid reference" message. Come back for extra help (in a ne
message).

It is advisable in such cases to have all files in the same folder whe
Excel takes care of this automatically, enabling them to be copie
anywhere without problem
 
F

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top