|
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用 · 内置多项VBA编程加强工具 ★ 免费下载 ★ ★ 使用手册★
先设置信任VBA
以下
Sub RemoveVBAFromMultipleWorkbooks()
Dim fd As FileDialog
Dim wb As Workbook
Dim wbPath As Variant
Dim vbComp As Object
Dim result As VbMsgBoxResult
' 提示用户选择多个Excel文件
Set fd = Application.FileDialog(msoFileDialogFilePicker)
With fd
.Title = "请选择一个或多个Excel文件"
.Filters.Clear
.Filters.Add "Excel 文件", "*.xls; *.xlsm; *.xlsb"
.AllowMultiSelect = True
If .Show = -1 Then
' 用户选择了文件
Else
MsgBox "操作已取消。", vbInformation
Exit Sub
End If
End With
' 确认清除操作
result = MsgBox("此操作将删除所选工作簿中的所有VBA代码,是否继续?", vbYesNo + vbExclamation, "警告")
If result = vbNo Then Exit Sub
' 遍历选择的文件
For Each wbPath In fd.SelectedItems
' 打开工作簿
Set wb = Workbooks.Open(wbPath)
' 检查是否含有VBA代码
If wb.HasVBProject Then
On Error Resume Next
' 删除所有模块
For Each vbComp In wb.VBProject.VBComponents
Select Case vbComp.Type
Case 1 ' 模块
wb.VBProject.VBComponents.Remove vbComp
Case 2, 3 ' 表单或类模块,清空代码
vbComp.CodeModule.DeleteLines 1, vbComp.CodeModule.CountOfLines
Case 100 ' ThisWorkbook 或 Sheet,对象模块,清空代码
vbComp.CodeModule.DeleteLines 1, vbComp.CodeModule.CountOfLines
End Select
Next vbComp
On Error GoTo 0
' 保存并关闭工作簿
wb.Save
Else
MsgBox "文件 " & wb.Name & " 不包含VBA代码。", vbInformation
End If
wb.Close
Next wbPath
MsgBox "所有操作已完成!", vbInformation
End Sub |
|