There doesn't appear to be a way of triggering an indivual query refresh then have VBA wait for it to complete before proceeding to the next one I'd like to be able to post a message with success/failure to alert the user that a query has failed due a problem with one of the data files I have a range of data files and I would like to loop through triggering a refresh on each of them, one by one. As the PQ is tightly bound to the data source it will just fail Some data files come from external clients so there is every chance one day some bright spark will decide to add a column. This will be seen by looking in the PQ window. The thing that concerns me is that should any of the data files change in anyway then the respective workbook query will fail. If I had to manipulate all of these files by VBA I'd be at it for months. I've built quite a large data model which loads up around 40 data files, each transformed using PowerQuery then loaded into data model.
But regardless, it can still be useful as a stand alone routine if you want to avoid having to train users on how to do things through the ribbon. I write a lot of VBA to make my users lives easier, and generally use this kind of technique as part of a bigger goal. Test it again by adding some more data to the table then clicking the button.
NOTE: The code above was updated to reflect the method posted here: LTest = InStr(1, cn.OLEDBConnection.Connection, "Provider=.1", vbTextCompare)
' Macro to update my Power Query script(s)ĭim lTest As Long, cn As WorkbookConnection In the window that pops up, paste in the following code:.Right click that name and choose “Insert Module”.Find your project in the list (It should be called “"VBA Project (Selecting Data.xlsx)”.If you don’t see a folder tree at the left, then press CTRL+R to make it show up. This will open the Visual Basic editor for you. But to use it, you need to know the secret handshake: Rather than record and tweak a macro, I’m just going to give you the code that will update all Query Tables in the entire workbook in one shot. Next, we need to build our VBA for refreshing the table. Let’s sort Animal ascending (just so we know something happened)Īt this point, we should get a new “Sheet2” worksheet, with our table on it:.Let’s just grab the sample data file from my post on pulling Excel named ranges into Power Query. The interesting part about the above statement is that Power Query doesn’t have any VBA object model, so what kind of black magic trick do we need to leverage to pull that off? As it turns out, it’s very simple… almost too simple in fact. The last thing I want to do is to send them right clicking and refreshing everywhere, so I program a button to refresh Power Query with VBA. When I’ve finished building a solution in Excel, I like to give it a little polish, and really make it easy for my users to update it.