Download did not complete VBA & Power Query

[cknnugget]

Using VBA to update power query I keep running into Query updating issues on protected sheets any Ideas on solutions?

When the sheet is protected, I get "Download did not complete".
The query says "you cannot edit a query that has been loaded to a protected worksheet"

When the sheet is Unprotected, the code runs without issue.

Under Data>Properties, Enable Background refresh is unchecked.

Here is the Code:
Sub Memolookup()
Sheets("Memo Look Up").Unprotect Password:=""
ThisWorkbook.Connections("Query - Memo Lookup").OLEDBConnection.refresh
Range("E4") = Range("B8").Value
Range("E5") = Range("C8").Value
Sheets("Memo Look Up").Protect Password:=""
End Sub

 

[Michael M]

That is how sheet protection works,leave out the password statement

Sub Memolookup()
Sheets("Memo Look Up").Unprotect
ThisWorkbook.Connections("Query - Memo Lookup").OLEDBConnection.refresh
Range("E4") = Range("B8").Value
Range("E5") = Range("C8").Value
Sheets("Memo Look Up").Protect
End Sub

 

[cknnugget]

My understanding is when Enable background refresh is unchecked, the query completes before moving to the next line in the VBA code. I have had other sheets where power query was updated without issue on a protected sheet. In this workbook it is protecting it before the query is complete. The password statement has a password, I just removed the password to post on the forum.

 

[Michael M]

Have you tried disabling background refresh ?
That way the code won't continue untile the refresh is completed.

 

[cknnugget]

Have you tried disabling background refresh ?
That way the code won't continue untile the refresh is completed.

Yes still no luck

 

[Michael M]

Sorry, I can't offer any more, unless you put in a wait statement for say 30 seconds and see if that helps.

Application.Wait Now + #0:00:30#