Change the location of external data feeding a pivottable

[Guest]

Some time ago I created a pivottable which pulls data from an external MS
Access database. I need to move the database to another directory.

I know that if I select "Pivottable Wizard" and "Back" I can redefine the
source of my external data, but it seems that I need to redefine the entire
connection (source, query, fields etc) and I'm not sure I can remember
everything I did. Is there a way I can change just the location of the
source data, without going through the entire linking process ?

 

[Guest]

See the Pivot Play section of Debra Dalgleish's website:
http://www.contextures.com/xlPivotPlay01.html

One of Pivot Play's main purposes is to redirect the query source.

Is that something you can work with?
***********
Regards,
Ron

XL2003, WinXP

 

[PatK]

Is there anything like this for Excel 2007? I assume this add-in would not
work with 2007? Or is there another way? I have what I think is similar
issue:

Each month I need to point the same excel file to a different access DB
(format of the table I am linking to is always constant, regardless of the DB
source).

Ideas?

PatK

 

[Ron Coderre]

You are correct: Pivot Play does not work with Excel 2007.
I'm not sure if the process got easier from Excel 2003 to 2007.
I don't use Excel 2007, so I can't supply a solution or alternative for you.

***********
Regards,
Ron

XL2003, WinXP

 

[PatK]

I actually did figure this out. Click on the pivot table on the worksheet.
The, go to:
- Data
- Connections
- click on the connection you wish to change the source for, and choose
Properties button
- Select the data tab

From there you can either browse to the access file, or, simply update the
connection string.