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

[kcmuppet]

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]

Maybe

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

 

[kcmuppet]

Maybe

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

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]

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]

Disable background refresh manually or via code and then refresh:

View attachment 28375

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

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]

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