A workbook protection with macro linked to a button is preventing power query to finish its load

Question

I have an Excel file with a Power Query connected to an SQL server. This file is distributed to various managers in the organization, each with their own unique role and therefore each excel is tailored to their user. To prevent misuse of the query, I use Workbook protection, so that the users can't modify the queries and download data not in their remit. However, to allow the users to download updates to the worksheet from the SQL, there's a button which they can click and Power Query refreshes the table. That button is linked to a VBA code (note that I've also protected the VBA code from being viewed):

Private Sub Update_Click()

    ThisWorkbook.Unprotect Password:="Password"
    Range("B6").Select
    Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
    Range("A1").Select
    MsgBox "Update complete!"
    ThisWorkbook.Protect Password:="Password"
End Sub

The problem I'm having is that Power Query waits for this macro to finish before it refreshes the end table in the worksheet, so even if it can connect to the SQL and fetch the data, for some reason it hangs indefinitely until the macro is complete and only then tries to refresh the worksheet's table (at B6). This leads to "Download not complete" error message on the query, because enabling workbook protection at the end of the macro prevents further updates. If I take the last bit of the code off, where it doesn't re-protect the workbook, Power Query finishes the table refresh correctly.

Any ideas how I can get the table refresh completed before the protection kicks in?

YariLei YariLei · Accepted Answer · 2020-10-16T11:14:30

I managed to find a solution to this! Basically what I had to do is make the protection re-application as a separate event. I put dual-password protection on it; one for running the code and one for the general protection and then I left the VBA command open on the protection part. Then, I made another command which follows changes to the worksheet and two more that run when the workbook is opened and when it is closed. To the user, these all appear as if the workbook constantly has a lock on it.

The code in the Sheet is this:

Private Sub Update_Click()

Dim passwordDos As Variant
          passwordDos = Application.InputBox("Enter Password", "Password Protected")

Select Case passwordDos
Case Is = False
                  'do nothing
Case Is = "PASSWORD"    'this unlocks the sheet update
    ThisWorkbook.Unprotect password:="protection"
    Dim con As WorkbookConnection
    Dim Cname As String

    For Each con In ActiveWorkbook.Connections
        If Left(con.Name, 8) = "Query - " Then
            Cname = con.Name
                With ActiveWorkbook.Connections(Cname).OLEDBConnection
                    .BackgroundQuery = False
                    .Refresh
                End With
        End If
    Next
    Application.CalculateUntilAsyncQueriesDone
    Do While Application.CalculationState <> xlDone
        DoEvents
        Loop
    MsgBox "Update complete!"
Case Else
    MsgBox "Incorrect Password"
End Select

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not ThisWorkbook.ProtectStructure Then ThisWorkbook.Protect password:="protection"
End Sub

Then, to the workbook level VBA project, I inserted this:

Private Sub Workbook_Open()
If Not ThisWorkbook.ProtectStructure Then ThisWorkbook.Protect password:="$uominenICT"
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If Not ThisWorkbook.ProtectStructure Then ThisWorkbook.Protect password:="$uominenICT"
ThisWorkbook.Save
End Sub

Note here, using just "ThisWorkbook.Protect" on a protected workbook seems to have an odd side-effect where instead of protecting, it will unprotect it. Makes no sense, so that must be a bug.

Basically, the Workbook_Open() command forces the workbook structure to be protected when the file is first opened. Similarly, BeforeClose will force the workbook to be protected before it is closed. Worksheet SelectionChange monitors the worksheet which contains the end table, so when that table updates, the same protection kicks in. That will always happen after the query is finished. The idea behind having the program ask the end user for another password is that clicking the button and user doing something unexpected may leave the worksheet in an unprotected state, so I wanted to make sure that it will generate a threshold on the VBA base when it will go forward with the update but otherwise keep it locked. I might actually add another line where it first checks the connection to the server before unlocking the workbook, just in case there's an error in the SQL connection. However, the idea here is that only the managers know the update password, so once they close the workbook and pass it to people who wouldn't be allowed to see anything else than what's in the specific workbook, those other people won't be able to run any updates nor see any codes. That's also where VBA view protection is handy.

A workbook protection with macro linked to a button is preventing power query to finish its load

1 Answers