Refreshing a query from another query

So I'll start out by saying that this blog has been a life saver. I just found the amazing gift of Power Query and had it not been for this blog I would have been stumbling in the dark to try and figure it out. So thank you!

That being said I'll quickly explain my situation and predicament. I'm working on an office computer which unfortunately is a 32-bit system with a whopping 4 gigs of RAM. I quickly realized that once I hit a certain data threshold in Power Query I get the dreaded out of memory issues. To try and counter this I decided to create some of my more complex queries in a separate workbook and once I have them all formatted the way I want them I would pull them into my main workbook thus, in theory minimizing the load on my poor computer. However, the problem that has ensued is the primary workbook, that I've pulled the backend query into won't refresh when the data changes. In an attempt to make it clearer:

I have two workbooks. The primary or frontend is called "Dashboard." The backend that I want to pull into the Dashboard is called "POSSX." POSSX consists of 11 queries that I've combined/transformed into a singular query. I then opened my Dashboard workbook, Get External Data from Excel, pull in the POSSX query and I now have 1 query in my Dashboard as opposed to the 11 in POSSX, thus preventing the memory issues. The problem now is that when the data changes that feeds the POSSX workbook/query it will update in that workbook if I open it and refresh all but without doing that, it won't update in the Dashboard workbook.

My question then is there a way to update the POSSX query in the Dashboard workbook without having to open the POSSX workbook first? Basically I want to link a query from one workbook into another and have it refresh in my frontend without having to manually open and refresh the backend first. If all that was confusing please let me know.

Thank you for any help in advance.

Tyler

Hi Tyler,

Unfortunately the remote workbook must be opened in order to refresh the Power Query. There's no way around that.

You do have options though. You could use a VBA macro to open the other workbook, refresh the data in that file, save it and close it. Once done, then you could kick off the refresh in the current workbook.

Having said that, if memory is an issue you could also use VBA to refresh queries in a specific order from the current workbook, not moving on to the next until the first is complete (and has released the memory.) It would be slower, but could get you where you need to be.

Thanks Ken. That was actually going to be my next thing to try, just wanted to see if there was something I was missing first. Greatly appreciate the response and the blog.

Hi Tyler
I came across this code the other day for creating a Query Event in a Class Module which might be of use.
Regards
Malcolm