Results 1 to 7 of 7

Thread: Update Power Query from a Different Workbook

  1. #1
    Seeker Zippymouse's Avatar
    Join Date
    Jul 2019
    Posts
    5
    Articles
    0
    Excel Version
    2019

    Update Power Query from a Different Workbook



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

    Hey there,

    I'm unsure if this is possible, but perhaps someone can point me in the right direction?

    At my work I have Excel workbooks that pull in weekly sales data for each of our locations (18 of them) using Power Query. There is then a seperate workbook that pulls in sales data from those 18 sheets and consolidates them for the company as a whole.

    My question is: is there a way for my consolidated workbook to automatically tell each of the 18 workbooks that it pulls from to update their Power Queries without me having to manually open and refresh each of them?

    Thanks!

  2. #2
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,652
    Articles
    0
    Excel Version
    O365
    Not sure if you could do it with PQ, but it would be simple for a VBA macro to open them one by one, refresh, and then close.

  3. #3
    Seeker Zippymouse's Avatar
    Join Date
    Jul 2019
    Posts
    5
    Articles
    0
    Excel Version
    2019
    Good point! I was so set on using PQ that I didn't try that first. Thanks!

  4. #4
    Seeker Kolyu's Avatar
    Join Date
    Oct 2018
    Location
    Sofia, Bulgaria
    Posts
    14
    Articles
    0
    Excel Version
    2007, 2010, 2013, 2016
    Let me see whether I get it right
    You have:
    1. source files named A, B, C....M - one for each location.

    2. A file on your PC, let's say X that pulls sales data from A,B...,M in 18 different sheets.
    3. Second file (Y) again on your PC that combines the 1 sheets from the first into one

    If this is the case, the second file is not needed. You can append 18 sheets into one with few additional steps in file X

  5. #5
    Seeker Zippymouse's Avatar
    Join Date
    Jul 2019
    Posts
    5
    Articles
    0
    Excel Version
    2019
    Hey Kolyu,

    Thanks for the suggestion, but unforutnately we do need to maintain the sheets for individual sites, and as a summarized version. My goal is to allow the summarized version to update all the worksheets at once, instead of opening them one-by-one to udpate.

    Thank you,
    Patrick

  6. #6
    Seeker Kolyu's Avatar
    Join Date
    Oct 2018
    Location
    Sofia, Bulgaria
    Posts
    14
    Articles
    0
    Excel Version
    2007, 2010, 2013, 2016
    As soon as all queries are in one file you can refresh them all at once by clicking on Refresh All button

  7. #7
    Acolyte El Cid's Avatar
    Join Date
    Aug 2016
    Location
    Greenville, SC
    Posts
    50
    Articles
    0
    Excel Version
    Excel 2016
    Quote Originally Posted by Zippymouse View Post
    Hey Kolyu,

    Thanks for the suggestion, but unforutnately we do need to maintain the sheets for individual sites, and as a summarized version. My goal is to allow the summarized version to update all the worksheets at once, instead of opening them one-by-one to udpate.

    Thank you,
    Patrick
    Bob is right, you'll have to VBA this one.

Posting Permissions

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