Pivot Table Datasource

[Charles]

I have a pivot table link to a query in a MAccess
database. Took me a long time to format the report...
Now, when I move the excel file on the server with the
database, the pivot table still point to the one on my C:
drive. How can I change the datasource without creating
a new pivot table and reformating?

even when I reassign a
new "ActiveWorkbook.PivotCaches.Connection" string, and
refresh, doesn't seem to work.

Thanks!

 

[TroyW]

Charles,

There are two changes that need to be made to reference a different external
Access database file.

(1) ActiveWorkbook.PivotCaches(1).Connection =
"your_modified_connection_string"
(2) ActiveWorkbook.PivotCaches(1).CommandText =
"your_modified_commandtext_string"
(Important: You must change them in this order)

The SQL CommandText string will have embedded in the FROM clause the
filepath to the Access database, so it isn't sufficient to just change the
connection string. Note, in this simple example I've assumed the PivotCache
is Item 1. You will need to reference your PivotCache appropriately. Hope
this helps.

Troy