以下2--14行是从网上找的,加入后试验了一下,其结果是运行后宏模块未删除干净(7个模块中删除了6个,但以下语句的模块仍存在)。请教老师们给予修改。谢谢!!! Sub 全部保存备份退出() With Workbooks(Workbooks.Count) '以下开始删除VBA代码 Dim vbcCom, Vbc Set vbcCom = .VBProject.VBComponents For Each Vbc In vbcCom If Vbc.Name Like "Sheet*" Or Vbc.Name Like "This*" Then Vbc.CodeModule.DeleteLines 1, Vbc.CodeModule.CountOfLines Else vbcCom.Remove (Vbc) End If Next Vbc ' .Save ' .Close End With '删除结束 ActiveWorkbook.SaveAs "E:\备份\" & Format(Now(), "yyyymmdd") & "日常统计(备份).xls" MsgBox "以当天日期命名的文件己保存到:E:\备份\...下, 现全部退出Microsoft Excel,再见!!!" Application.Quit Workbooks.Close '关闭所有打开的工作簿 End Sub
Sub 全部保存备份退出() Dim vbcCom Dim Vbc As Object Dim WSH As Object Dim strVBS As String strVBS = Replace(UCase(ThisWorkbook.FullName), ".XLS", ".vbs") ' Set WSH = CreateObject("Wscript.Shell") For Each Vbc In Application.ThisWorkbook.VBProject.VBComponents Select Case Vbc.Type Case 1, 2, 3 With Application.VBE.ActiveVBProject.VBComponents .Remove .Item(Vbc.Name) End With Case Else Vbc.CodeModule.DeleteLines 1, Vbc.CodeModule.CountOfLines End Select Next ActiveWorkbook.SaveAs "E:\备份\" & Format(Now(), "yyyymmdd") & "日常统计(备份).xls" MsgBox "以当天日期命名的文件己保存到:E:\备份\...下, 现全部退出Microsoft Excel,再见!!!" Set fso = CreateObject("Scripting.FileSystemObject") Set tf = fso.CreateTextFile(strVBS, True) '创建temp文件VBS文件 With tf '写入VBS文件内容 .WriteLine ("Set msexcel=GetObject(,""Excel.Application"")") .WriteLine ("msexcel.save") .WriteLine ("msexcel.quit") .WriteLine ("set fso=createobject(""scripting.filesystemobject"") ") .WriteLine ("fso.DeleteFile WScript.ScriptFullName") .Close End With WSH.Run Chr(34) & strVBS & Chr(34), 1, True End Sub
[此贴子已经被作者于2010-8-22 14:21:33编辑过]
|