指定路径Excel工作簿取消保护刷新连接后重新保护的报错解决

核心原因 #

• Power Query 无法直接读取设置了工作簿结构保护的文件，会误判文件损坏抛出[DataFormat.Error] File contains corrupted data.报错，取消保护后读取逻辑即可恢复正常。
• 你当前编写的VBA逻辑存在偏差：代码仅对每个子工作簿执行自身内部连接的刷新操作，没有触发主表的Power Query汇总刷新，无法匹配你的使用需求。

修正后执行逻辑 #

批量解锁所有子工作簿 → 触发主表Power Query全部刷新 → 批量重新给子工作簿加保护

Option Explicit
Sub Unlock_Refresh_Master()
    Dim wb As Workbook, ws As Worksheet
    Dim Filepath As String, Filename As String
    Dim n As Long
    Const pass = "1519"
    
    ' 读取子工作簿所在路径
    Filepath = Worksheets("Sheet2").Range("A1").Value
    If Right(Filepath, 1) <> "\" Then Filepath = Filepath & "\"
    
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    ' ----------------------
    ' 第一步：批量解锁子工作簿
    ' ----------------------
    Filename = Dir(Filepath & "*.xls*")
    Do While Filename <> ""
        ' 跳过主表自身，避免误操作
        If Filename <> ThisWorkbook.Name Then
            Set wb = Workbooks.Open(Filepath & Filename, Password:=pass)
            With wb
                .Unprotect Password:=pass
                ' 若子工作簿设置了工作表保护，可取消下方注释解锁对应工作表
                ' For Each ws In .Worksheets
                '     ws.Unprotect Password:=pass
                ' Next
                .Save
                .Close
            End With
            n = n + 1
        End If
        Filename = Dir
    Loop
    
    ' ----------------------
    ' 第二步：刷新主表Power Query
    ' ----------------------
    ThisWorkbook.RefreshAll
    ' 等待刷新完成，执行代码前请先关闭所有查询的后台刷新选项
    Do While ThisWorkbook.Connections.Count > 0
        If Not ThisWorkbook.Connections(1).OLEDBConnection.Refreshing Then Exit Do
        DoEvents
    Loop
    
    ' ----------------------
    ' 第三步：批量重新加保护
    ' ----------------------
    n = 0
    Filename = Dir(Filepath & "*.xls*")
    Do While Filename <> ""
        If Filename <> ThisWorkbook.Name Then
            Set wb = Workbooks.Open(Filepath & Filename, Password:=pass)
            With wb
                .Protect Password:=pass
                ' 若之前解锁了工作表，可取消下方注释重新加保护
                ' For Each ws In .Worksheets
                '     ws.Protect Password:=pass
                ' Next
                .Save
                .Close
            End With
            n = n + 1
        End If
        Filename = Dir
    Loop
    
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    MsgBox n & " 个工作簿已完成保护设置，主表数据刷新完成", vbInformation
End Sub

注意事项 #

• 执行代码前请先关闭所有Power Query的后台刷新：操作路径为「数据」→「查询和连接」→ 右键每个查询→「属性」→ 取消勾选「允许后台刷新」，确保刷新完成后再执行后续加锁操作。
• 请确保Sheet2的A1单元格填写的是4个子工作簿所在的完整文件夹路径，不要填写主表自身的存储路径。

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