How do I refresh Pivot Table from an external source ?

[Guest]

I have a Pivot Table in the workbook A, and then a table in workbook B, that
is updated once a month. I then make a Pivot Table in wbk B, based on the
base table, then copy the Pivot Table from wbk B to wbk A, replacing the old
one in wbk A.
I need to be able to refresh PT in wbk A without replacing it, because links
to many other tables in wbk A get lost.

I tried to go to :

Data > Pivot Table and Pivot Charts Report > hit Back twice > Select
External Data source, hit Next > Get Data > Excel Files * > select the file >
But I don't get the options to select the tables or the fields, moreover I
get an error something like "this is not an excel file" or "no tables were
found".

Please help! Thanks!

 

[JP]

Why not write a macro to create the Pivot Table and copy it into wkbk
A, and destroy the old one, as needed?



HTH,
JP

 

[Ron Coderre]

If your Pivot Table is based on an External Datasource,
the Pivot Play add-in (free), available at the below site
was designed to alter the source location and/or the query:

http://www.contextures.com/xlPivotPlay01.html

Does that help?
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)

 

[JP]

Sorry, didn't read your note properly.

If you create the Pivot table directly in wkbk A, you should be able
to refresh it by hitting the "!" refresh button on the PivotTable
toolbar. If you are using VBA, try

ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh

Assuming you have selected the appropriate sheet, and the name of the
pivot table is PivotTable1.


HTH,
JP