Pivot Tables - Change source from internal to external

[syswizard]

I have a boss who wants to re-architect all of the corporate reporting.
Currently this is done via Pivot Tables with data sources in the same workbook.
He wants to Change the data source to be another workbook on the network.
Via the GUI, Excel disables the external connections option for existing pivot tables.
So I tried to use the VBA "ChangeConnection" method to do so. That bombed on a 1004 error.
So it appears there is no other way to do this other than deleting the old one and re-creating a new pivot table ?
Am I correct ?

[Rudi]

I don't think you need to use the External Connections option.
If you open the source file on the network, and you open the current file with the pivot table, then go to the Change Data Source dialog, clear the current source and while the cursor is flashing in the Table/Range box, switch to the new source (that you opened from the network) and highlight the range in this file. Click on OK and the pivot will now accept the new source and location.

[rory]

Or click in the pivot table, press Alt+DP, step back to step 1 of the wizard and choose 'External data' there.

[syswizard]

Thanks guys....it worked when I opened the data source workbook.
However, it is interesting: One can have a connection established to a workbook or a direct reference to the same workbook in a different pivot table.
I imagine the direct reference is better ?
Anyone test this one way or the other ?

[Rudi]

Just my opinion, but if the source is a range in Excel (no matter it being in the same WB as the pivot or external), it is better to have a direct reference. If the source is anything other than a range it is better to go by data connection.

[syswizard]

thanks Rudi - but the huge issue is one of dependence: the direct connect requires that the data source workbook be open when the pivot table workbook is refreshed !!!
That is not required with a connection.
However, in this case, we don't want the users refreshing the data......so this is perfect.

[Rudi]

Glad to have helped resolve the query.

Regarding your statement that "the direct connection requires that the data source workbook be open when the pivot table workbook is refreshed..."
This is not true. If I open a workbook containing a pivot whose source is in a closed workbook, (as long as I have acknowledged that the workbook can update external links), then, on refreshing the pivot, the data updates.

Cheers

[syswizard]

"RE: as long as I have acknowledged that the workbook can update external links), then, on refreshing the pivot, the data updates. "
Not with source data as a TABLE reference.
result in 2010: "Invalid Reference".
Open the data workbook and that goes away.
Likely a bug.

[Rudi]

TX. Never tested with a table.

[rory]

Tables are effectively dynamic ranges and, as such, can't be resolved unless the parent workbook is open.

[Rudi]

TX Rory; that makes sense

[syswizard]

Tables are effectively dynamic ranges and, as such, can't be resolved unless the parent workbook is open.

Correct, however this is a bug/limitation in that the Excel architects could have used the range stored in the Names section and then returned that range when requested by another workbook. All tables have a Named reference containing the current range.
Could-a, Should-a, Would-a.

[syswizard]

More bugs: when trying to create a new Pivot Table referencing the tablename, you must first select some cells in the worksheet where the table resides.
Once that is done, remove the cell references and replace with the Tablename. Viola.....Excel changes the reference completely.
Try typing it in directly: Invalid Reference is thrown.

[syswizard]

Even more bugs: Pivot table "RefreshTable" method seems to fail.....silently.
Can anyone tell me the difference between:

PivotCache.Refresh and PivotTable.RefreshTable

[HansV]

As far as I can tell there is no difference - see your thread PivotTable - RefreshTable vs. Refresh PivotCache from last year.

[syswizard]

Thanks for reminding me Hans.
Still, with a TABLE as the source data reference, an open workbook does not seem to get it's Pivottables refreshed via a RefreshTable method call from another workbook.
I noticed that the RefreshTable method does return a true/false return value whereas Refresh.PT.PivotCache does not.
I have never tested the return value....I guess I should now.
However, does a false return indicate the pivottable has completed the refresh... or some other problem ?

[HansV]

I don't know the exact ins and outs of RefreshTable - I hope someone else knows more about it.

The PivotTable.RefreshTable method returns True if the table was updated successfully, False otherwise. If False, it doesn't provide any information as to why the update failed.

To confuse matters even further, the PivotTable object also has an Update method. I have no idea what that one does - it doesn't update the pivot table when the source data have been changed.

[rory]

To confuse matters even further, the PivotTable object also has an Update method. I have no idea what that one does - it doesn't update the pivot table when the source data have been changed.

It updates the pivot table if you have it set to defer updating the layout (the Manualupdate = true option in code).

[HansV]

Ah - thanks!

[syswizard]

Thanks Rory, but I think the Update method only changes/updates the pivotfields settings and properties and the pivottable layout (which fields are row, column, page, etc). I don't think it has anything to do with the refresh of the data source.

An interesting observation:
if RefreshTable and Pivotcache Refresh were indeed the same methods functionally, why would one have a return value and the other none ?