# vba to re-protect sheet after Power Query has finished Refreshing



## kcmuppet (Dec 20, 2020)

Hi - 

I'm using this code to refresh a query data on a protected sheet .  The problem is , the code starts refreshing the query, then re-protects the sheet before the data starts to load to the sheet, and then I sometimes get the warning "The cell...is on a protected sheet...." , and the so the Table doesn't update.


```
Sub Refresh_Report()
        
Sheets("Report").Unprotect Password:="123"

ActiveWorkbook.Connections("Query - Full Report").Refresh

Sheets("Report").Protect Password:="123"

End Sub
```

What do I need to add after to the connection refresh to make Excel wait until the data is loaded to the sheet before re-protecting it?


----------



## Worf (Dec 20, 2020)

Maybe


```
Application.Wait TimeValue("0:00:10") + Now()
```


----------



## kcmuppet (Dec 20, 2020)

Worf said:


> Maybe
> 
> 
> ```
> ...


Thanks for your reply. The trouble is, the queries don't take a given amount of time. It's highly variable dependent on the data in the sheets, the pc power, network, etc. I

s there a way to know when the query has finished?


----------



## Worf (Dec 21, 2020)

Disable background refresh manually or via code and then refresh:







```
Public Sub Refresh_All()
Dim i%, aw As Workbook
Set aw = ActiveWorkbook
For i = 1 To aw.Connections.Count
    If aw.Connections(i).Type = 1 Then _
    aw.Connections(i).OLEDBConnection.BackgroundQuery = 0
Next
ThisWorkbook.RefreshAll
DoEvents
MsgBox "Done"
End Sub
```


----------



## kcmuppet (Dec 22, 2020)

Worf said:


> Disable background refresh manually or via code and then refresh:
> 
> View attachment 28375
> 
> ...


Hi, thanks for your reply.

Am I right in thinking this stops _other_ queries refreshing while this one refreshes? Or does "Type =1" mean in it's _this_ connection?


----------



## Worf (Dec 23, 2020)

With this method the queries are refreshed sequentially. Type 1 means an OLE connection, which is what you get for Power Query. 
The code below worked for me:


```
Public Sub Refresh_All()
Dim i%, aw As Workbook
Set aw = ActiveWorkbook
For i = 1 To aw.Connections.Count
    MsgBox aw.Connections(i).Name & vbLf & aw.Connections(i).Type
    If aw.Connections(i).Type = 1 Then _
    aw.Connections(i).OLEDBConnection.BackgroundQuery = 0
Next
Sheets("Report").Unprotect Password:="123"
ThisWorkbook.RefreshAll
DoEvents
Sheets("Report").Protect Password:="123"
MsgBox "Done"
End Sub
```


----------

