Results 1 to 6 of 6

Thread: External Data Source for multi-PivotTable

  1. #1

    Question External Data Source for multi-PivotTable



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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?

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,225
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    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.
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training) or with my book M is for Data Monkey!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    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

  4. #4
    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.

  5. #5
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,225
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    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.
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training) or with my book M is for Data Monkey!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

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

    Sent from my phone using Tapatalk

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •