Refreshing PowerQuery Tables on a Protected Sheet

I have a workbook with 6 PowerQuery tables, and one PowerQuery table to append the six into the one table.

The six are on hidden, unprotected sheets. However, I have the appended table on protected sheet.

When I have the protection off and I run my refresh macro the table updates fine. However, when the sheet is protected the table does not update.

My macro is set to unprotect the sheet, refresh, and then reprotect. However, what is is doing is; unprotecting, refreshing, and then reporotecting before the refresh is done.

I've tried adding in a wait before reprotecting, but this didn't help.

It seems my problem is that after the macro has finished running, the PowerQuery tables are still updating. How can I unprotect the sheet, refresh the tables, wait until the refresh is complete, and then reprotect the sheet?

I have the below as my macro to refresh, where/how do I specify the Backgroundquery:=False argument?

Please Login or Register  to view this content.

Not sure how I'd do that to be honest. This is my first time using PowerQuery – only downloaded the add-on for 2010 yesterday.

Is this the Query pane you mean? After I run my macro it says that the one on the protected sheet didn't download.

Query.PNG

The only options i get in properties are: Name, Description, and then a check box for Fast Data Load.

Still no joy unfortunately.

I'm now thinking I can do it with 4 macros:

1. unprotect the sheet and refresh
2. wait until refresh is done (either a time delay, or ideally something to say that the refresh is now finished)
3. protect the sheet
4. run macros 1, then 2, then 3

The option was there, but it still resulted in the the download not being complete.

I missed CK76's code. I don't understand it. is the "Query -" part supposed to be the name of the table?

Got it working.

Had to make all the table not refresh in the background, and then add in a DoEvents before reprotecting the sheet.

Realised that I can refresh the main, appended table, without having to do all the ones on the hidden sheets. It still pulls through the correct data. This basically just cuts the time taken in half.

Please Login or Register  to view this content.

Thanks for your help guys!