Changing Data Source for Pivot Table

[jjj]

Hi,

I currently have a macro button which refreshes the pivot tables in my
report.
The data source for the pivots is a db which sits in a folder which is
no longer accessible for users to view.

I therefore have a copy of the db in a shared area. I need to relink
my pivot tables to the new db source (path).

Is there anyway of changing the source without completely re-doing all
of the pivots?

Many Thanks in Advance.
Jason

 

[Guest]

Yes there is a complex process.

Click on the pivot table wizard--> click back --> Get Data -->
At this stage an error msg will come on saying that the source data is not
found.
Click ok -->
Click on database button and select your database
Next msg appears "query cannot be edited by wizard"--->hit OK
Query screen opens
File-->New-->Select ODBC connectivity-->Select your databased file--> select
your table and then continue as usual.

See if this works... I generally use MS Access Database on which the Excel
pivots are built.

Manoj