External Data Source for multi-PivotTable

I created many pivot tables in a single excel file and it is connected to external data source.
They are come from the same data source file.
In stead of changing the external data source file one-by-one, any shortcut for these steps?

You could write a macro to do this. I assume that you're making a backup and new copy?

To be honest, the better route would be to actually pull your data into one table in the workbook if you can, then build all of your pivottables off that. That way it's only one connection to your data source. If your data source is too big though, well then you're stuck.

What I'd suggest is to record a macro of the actions you take to update it, then post that here. We can then modify the macro to pick up all pivottables in the file.

Do you know how to record a macro? If not, let me know and I'll walk you through it.

Let me explain more detail about my problem.

I have to use a external excel data file which is updating daily by other people. Hence, I need to connect as external data source so that I can always have the latest data.

My pain point is that, the data source filename will be changed every week. So, I have to change the pivot tables data source manually. This is quit inconvenient and time consuming because I have many pivot table report.

I just thinking if there is any global setting for external source for multi pivot table.

Sent from my phone using Tapatalk

Rather than having a constantly changing data source, why don't you get the database people to create a named copy in set place each week after updating the data source, and then you connect to that. ALl you need to do then is to refresh the pivot each week.

I would tend to agree with Bob on this.

Regardless if, for whatever reason, you can't do that, then here's what we'd need.

• If you haven't got the Developer tab showing, go into File --> Options --> Customize Ribbon and check the box next to "Developer" on the right.
• Go to the Developer Tab and click "Record Macro"
• Update the source of one of your PivotTables as you usually would
• Go back to the Developer Tab and click "Stop Recording"
• On the Developer Tab, click Macros --> Select Macro1 (or whatever you called it) --> Edit
• Copy all of the code and post it here

When you post the code, wrap it in code tags like this:

[Code] all your code goes here [/code]

That will format it nicely so that we can read it easily.

From there, we should be able to see enough about your data connections to clean it up and make a macro to update them all.

Thanks Bob and Ken's suggestions. Both are practical and feasible.

Sent from my phone using Tapatalk