excel - 带有链接到按钮的宏的工作簿保护正在阻止电源查询完成其负载

Question

我有一个带有连接到 SQL 服务器的 Power Query 的 Excel 文件。该文件分发给组织中的各个经理，每个经理都有自己独特的角色，因此每个 excel 都是为他们的用户量身定制的。为了防止滥用查询，我使用了工作簿保护，这样用户就不能修改查询和下载不在他们职权范围内的数据。但是，为了允许用户从 SQL 下载工作表的更新，他们可以单击一个按钮，Power Query 会刷新表格。该按钮链接到 VBA 代码（请注意，我还保护了 VBA 代码不被查看）：

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

我遇到的问题是 Power Query 在刷新工作表中的结束表之前等待此宏完成，因此即使它可以连接到 SQL 并获取数据，由于某种原因它会无限期挂起，直到宏完成，然后才尝试刷新工作表的表格（在 B6 处）。这会导致查询中出现“下载未完成”错误消息，因为在宏末尾启用工作簿保护会阻止进一步更新。如果我关闭代码的最后一点，它不会重新保护工作簿，Power Query 会正确完成表刷新。

有什么想法可以在保护开始之前完成表刷新吗？

标签： excelvbasecuritypowerquery

Answer 1

我设法找到了解决方案！基本上我要做的就是将保护重新应用作为一个单独的事件。我对其进行了双重密码保护；一个用于运行代码，一个用于一般保护，然后我在保护部分打开 VBA 命令。然后，我制作了另一个命令，该命令跟随对工作表的更改以及另外两个在工作簿打开和关闭时运行的命令。对用户来说，所有这些看起来都好像工作簿一直在锁定它。

Sheet中的代码是这样的：

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

然后，在工作簿级别的 VBA 项目中，我插入了这个：

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

请注意，在受保护的工作簿上仅使用“ThisWorkbook.Protect”似乎有一个奇怪的副作用，它不会保护它，而是取消保护它。没有意义，所以这一定是一个错误。

基本上，Workbook_Open() 命令强制在文件首次打开时保护工作簿结构。同样，BeforeClose 将强制工作簿在关闭之前受到保护。Worksheet SelectionChange 监视包含最终表的工作表，因此当该表更新时，会启动相同的保护。这将始终在查询完成后发生。让程序向最终用户询问另一个密码背后的想法是，单击按钮和用户做一些意外的事情可能会使工作表处于不受保护的状态，所以我想确保它会在 VBA 基础上生成一个阈值。将继续更新，否则保持锁定状态。我实际上可能会添加另一行，在解锁工作簿之前首先检查与服务器的连接，以防万一 s SQL 连接出错。但是，这里的想法是只有管理员知道更新密码，所以一旦他们关闭工作簿并将其传递给除了特定工作簿中的内容之外不允许看到任何其他内容的人，其他人将不会能够运行任何更新也看不到任何代码。这也是 VBA 视图保护很方便的地方。