Update Power Query from a Different Workbook

[Zippymouse]

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!

 

[Bob Phillips]

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.

 

[Zippymouse]

Good point! I was so set on using PQ that I didn't try that first. Thanks!

 

[Kolyu]

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

 

[Zippymouse]

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

 

[Kolyu]

As soon as all queries are in one file you can refresh them all at once by clicking on Refresh All button

 

[El Cid]

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.