Hi,
I have workbooks containing numerous Power Queries and I use VBA to update this as required. The user click a button to trigger this. I have many formulas in my workbook which i don't want destroyed by the users so I want all my worksheets protected.
So the code approach is to...
- loop through all worksheets and unprotect them
- loop through and refresh all queries in the workbook
- loop through all worksheets and protect them.
This issue is that step 3 will run before the queries have finished refreshing in Step 2. I've tried adding Application.CalculateUntilAsyncQueriesDone at the end of routine 2 above, hoping that it would pause the code until 2 is finished before moving to Step 3.
QUESTION: In short, is there a way for vba to pause until queries are refreshed?
MY WORKAROUND: use code to hide all worksheets except one page that contains a menu. The worksheets are not protected but are only accessible one by one from the menu sheet. As a sheets is selected it is protected, then unhidden. As a user returns to menu sheet, the sheet they were on is hidden then unprotected. That way all sheets are permanently unprotected (until viewed) so that queries can be run unobstructed from protection on the sheets they are connected to. But not ideal really.
Kind regards,
Greg
Hi Greg,
You have 2 options to refresh a connection: with background refresh set to false or to true.
Go to Data-Connections, select the connection you want, click on Properties, and uncheck the checkbox "Enable background refresh"
This way, the next line will not be executed until the refresh is complete.
If you have many connections, use a simple code to disable that option:
Dim Connection As Variant
For Each Connection In ActiveWorkbook.Connections
Connection.OLEDBConnection.BackgroundQuery = False
Next Connection
End Sub
You will get such code if you use the macro recorder while performing this action manually for a connection.
Hi Catalina,
Thanks for this. Actually I do have that code in my routines and I run that code whenever the workbook opens (in case a future user/developer manually changes the settings). I had done a lot of googling on this and come up with this... https://www.excelcampus.com/library/enable-background-refresh-on-all-power-query-connections/. The code you mention is useful as it seems to help the queries run sequentially which means the workbooks in question don't seem to freeze up. But it doesn't seem to stop the code moving to Step 3 before the last query in Step 2 refreshes.
I'll do some more testing and see what happens.
Thanks so much for your prompt reply.
Kind regards,
Greg
Indeed, this will not solve the last query refresh, and I know no way to capture that.
The only functional solution is to add a wait time after the refresh code, but you have to estimate the time correctly for this to work.
Use:
Application.Wait Now() + TimeSerial(0,0,10) to get a 10 seconds delay. Change as needed.
Ok, great. Thanks, Catalina.
At least you've confirmed what I've found so far which is helpful.
I tried to find code that would test if refreshing was in progress but with no success.
I prefer not to go with the Application.Wait.Now() + approach as (i) processing speeds can vary between users's computer (where multiple users use the same workbook model), and (ii) the underlying data files in folder may increase over time where the query is processing, say, daily lawyer timesheets.
I think it's something that Microsoft should address and I have friend here who constantly makes suggestions to them so I'll ask him how he goes about it.
Once again, thanks!
Kind regards,
Greg
I have the solution! Crude but should work...
When refreshing the queries programmatically, they seem to process in alphanumeric order based on the name of the queries. Therefore, create a permanent hidden dummy WorkSheet with a single row of data and then create a simple query on it and name the query "ZZZZZZZZZZZ_DummyQry" or something similar.
When the code loops through the queries, the "ZZZZZZZZZZZ_DummyQry" query will run last (and finish in a fraction of a second) ensuring that last genuine query is finished processing before moving onto the code to protect all worksheets because of the code line Connection.OLEDBConnection.BackgroundQuery = False.
Sounds good, glad to hear that you managed to solve it, looks like a good option.
