用VBA宏自动改变Excel宏安全级别设…

发布时间:2014-10-23 23:25:43
来源:分享查询网

用VBA宏自动改变Excel宏安全级别设置 Option Explicit Sub SetExcelVBA() '改变Excel的安全级别 '使用Wscript,FileSystemObject,创建txt文件,注册表操作,VBS文件自我删除,改变Excel文件读写属性等 Dim WSH As Object, ret As String, regStr As String Dim strFullname As String, strVBS As String Dim tf, fso, RetVal '本程序仅适用于Excel 2003(11.0),如果当前版本不是2003则退出 If Application.Version <> "11.0" Then MsgBox "本代码仅在 Excel 2003 下可使用! ", vbExclamation, "提示": Exit Sub strFullname = ThisWorkbook.FullName '取得当前工作薄的全名 strVBS = Replace(UCase(strFullname), ".XLS", ".vbs") 'temp文件VBS的文件名 Set WSH = CreateObject("Wscript.Shell") '创建Wscript对象 Err.Clear On Error Resume Next regStr = "HKEY_CURRENT_USER\Software\Microsoft\Office\11.0\Excel\Security\Level" '注册表中Excel vba安全级别位置 ret = WSH.RegRead(regStr) '读取当前安全级别 If Err.Number <> 0 Then    '判断读取是否成功    MsgBox "从注册表读取当前Excel VBA安全级别设置失败,本程序将退出! ", vbExclamation, "提示"    Exit Sub Else '如果当前Excel VBA安全级别不为“低”,则设置为“低”,值1-4分别对应:低,中,高,非常高 If Val(ret) <> 1 Then ret = WSH.RegWrite(regStr, "1", "REG_DWORD") End If Set fso = CreateObject("Scripting.FileSystemObject") Set tf = fso.CreateTextFile(strVBS, True) '创建temp文件VBS文件 With tf     '写入VBS文件内容     .WriteLine ("Dim oExcel,fso,delme")     .WriteLine ("Set fso = CreateObject(""Scripting.FileSystemObject"")")     .WriteLine ("Set oExcel = CreateObject(""excel.application"")")     .WriteLine ("oExcel.Workbooks.Open " & Chr(34) & strFullname & Chr(34))     .WriteLine ("oExcel.Visible=true")     .WriteLine ("Set oExcel = Nothing")     .WriteLine ("delme = fso.DeleteFile(" & Chr(34) & strVBS & Chr(34) & ")")     .Close End With With ThisWorkbook     '将当前文件属性设置为“只读”,以方便重新打开     .ChangeFileAccess Mode:=xlReadOnly     .Saved = True End With RetVal = WSH.Run(Chr(34) & strVBS & Chr(34), 1, True) '运行刚刚创建的VBS文件,新启动一个Excel程序 Application.Quit '退出当前Excel Set WSH = Nothing Set fso = Nothing End Sub

返回顶部
查看电脑版