如何通过VBA禁止用户刷新PowerQuery驱动的Excel表格（不中断链接）

以下是几种实用思路，可单独或组合使用实现需求：

1. 将PowerQuery设置为仅连接，由VBA控制数据写入 #

把查询的加载方式改为仅连接，让查询不再自动绑定并更新表格，完全通过VBA运行查询后将结果写入目标区域。用户无法通过右键或查询面板刷新表格，因为表格与查询无直接绑定：

• 编辑PowerQuery查询，在「加载到」选项中选择「仅创建连接」
• 用VBA运行查询并写入结果，示例代码：

Sub UpdateTableViaVBA()
    Dim conn As WorkbookConnection
    Dim rs As Object
    
    ' 获取目标查询连接
    Set conn = ThisWorkbook.Connections("YourQueryConnectionName")
    ' 刷新连接
    conn.Refresh
    
    ' 将查询结果写入目标表格（起始单元格为Sheet1!A1）
    Set rs = conn.OLEDBConnection.Recordset
    Sheet1.Range("A1").CopyFromRecordset rs
    
    ' 可选：保护工作表防止用户修改表格
    Sheet1.Protect Password:="YourSecurePassword", UserInterfaceOnly:=True
End Sub

2. 禁用表格右键菜单的「刷新」选项 #

通过VBA修改Excel右键菜单，移除或禁用表格右键中的「刷新」命令：

Private Sub Workbook_Open()
    DisableTableRefreshRightClick
End Sub

Sub DisableTableRefreshRightClick()
    Dim cmdBar As CommandBar
    Dim cmdCtrl As CommandBarControl
    
    ' 定位表格右键菜单
    For Each cmdBar In Application.CommandBars
        If cmdBar.Name = "List Range Popup" Then
            For Each cmdCtrl In cmdBar.Controls
                ' 匹配「刷新」命令（英文版本替换为"Refresh"）
                If cmdCtrl.Caption = "刷新" Then
                    cmdCtrl.Enabled = False
                    Exit For
                End If
            Next cmdCtrl
            Exit For
        End If
    Next cmdBar
End Sub

3. 隐藏PowerQuery查询，阻止用户在查询面板操作 #

将查询设置为隐藏，用户在PowerQuery查询面板中看不到该查询，自然无法手动刷新：

Sub HideTargetQuery()
    ' 替换为你的查询名称
    ThisWorkbook.Queries("YourQueryName").Visible = False
End Sub

注意：此方法仅隐藏查询，若用户知晓查询名称仍可通过VBA修改，建议搭配工作表保护使用。

4. 工作表保护限制用户操作 #

对包含目标表格的工作表设置保护，禁止用户编辑表格或触发刷新操作：

Sub ProtectWorksheet()
    With Sheet1
        ' 允许必要操作（如筛选），可按需调整
        .Protect _
            Password:="YourPassword", _
            UserInterfaceOnly:=True, _
            AllowFiltering:=True, _
            AllowSorting:=True
        ' 锁定目标表格单元格防止修改
        .ListObjects("YourTableName").Range.Locked = True
    End With
End Sub

UserInterfaceOnly:=True 确保VBA代码仍能正常操作工作表，无需每次刷新前解除保护。

内容的提问来源于stack exchange，提问作者phmauber