如何使用VBA在粘贴数据前解除外部Excel文件保护并操作后恢复保护

改后可用完整代码 #

Sub 同步表单数据到工作队列()
Dim main_workbook As Workbook, target_workbook As Workbook, ws As Worksheet, ws1 As Worksheet, ws2 As Worksheet
Dim r1 As String, r2 As String
Dim s0 As String, s1 As String, s2 As String, s3 As String
Dim next_row As Long, col_number As Integer, col_date As Integer, close_workbook As Boolean, sheet_protect_pwd As String

 '---------- 配置区开始 ----------

' 要粘贴数据的目标工作簿路径
Set target_workbook = Workbooks.Open("S:\3D Print Lab_Revised\Print Lab Queue.xlsm")

' 从当前表单工作簿复制的范围
'-------------------------------------
r1 = "A3:C3"
r2 = "A5:I17"
'-------------------------------------
s0 = "Main"   ' 复制数据的源工作表名称
'-------------------------------------
s1 = "FormLab Queue"   ' 粘贴r1范围的目标工作表名称
s2 = "Pro 2 Plus Queue"    ' 粘贴r2范围的目标工作表名称
s3 = "Archive Jobs"    ' 粘贴r3范围的目标工作表名称

col_number = 1 ' 粘贴的起始列号
col_date = 1 ' 日期写入的列号
close_workbook = True ' 宏运行结束后是否关闭目标工作簿
sheet_protect_pwd = "YYYY" ' 所有工作表统一的保护密码
'---------- 配置区结束 ----------

' 关闭屏幕刷新提升运行速度
Application.ScreenUpdating = False

' 第一步：解除目标工作簿所有工作表的保护
For Each ws In target_workbook.Sheets
    ws.Unprotect Password:=sheet_protect_pwd
Next ws

' 复制源工作表r1范围，获取目标工作表s1的下一个空行
ThisWorkbook.Sheets(s0).Range(r1).Copy
next_row = target_workbook.Sheets(s1).Cells(Rows.Count, 5).End(xlUp).Row + 1

' 粘贴r1的值到目标工作表s1，同时写入当前日期
target_workbook.Sheets(s1).Cells(next_row, 2).PasteSpecial Paste:=xlPasteValues
target_workbook.Sheets(s1).Cells(next_row, col_date) = Format(Now(), "MM-DD-YYYY")

' 这里可以继续添加你原来剩下的r2等范围的复制粘贴逻辑
' ---- 你原有其余业务代码写在此处即可 ----

' 第二步：所有数据写入完成后，重新保护所有工作表
For Each ws In target_workbook.Sheets
    ws.Protect Password:=sheet_protect_pwd
Next ws

' 保存目标工作簿
target_workbook.Save

' 恢复屏幕刷新
Application.ScreenUpdating = True

' 按配置判断是否关闭目标工作簿
If close_workbook Then
    target_workbook.Close
End If

End Sub

核心改动说明 #

• 把工作表保护密码统一放到配置区的sheet_protect_pwd变量中，后续修改密码只需要调整一处即可，无需逐行修改
• 打开目标工作簿后第一时间循环遍历所有工作表执行解除保护，后续新增工作表也会自动适配，无需额外修改代码
• 所有数据粘贴、写入操作完成后，保存工作簿前会再次循环遍历所有工作表重新开启保护
• 完全保留你原有复制、粘贴、写入日期的业务逻辑，不会影响现有功能的正常运行

可选调整说明 #

• 如果仅需要保护指定工作表，可以在遍历保护/解除保护的循环中增加工作表名称判断逻辑，过滤不需要操作的工作表
• 如果需要给保护的工作表开放部分操作权限（比如允许排序、筛选、选中单元格），可以在Protect方法后增加对应参数，例如ws.Protect Password:=sheet_protect_pwd, AllowSorting:=True, AllowFiltering:=True

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