[VBA]用VBA宏自动改变Excel宏安全级别设置
来源:互联网 发布:流量话费监控软件 编辑:程序博客网 时间:2024/05/18 12:39
Option Explicit
Sub SetExcelVBA()
'练习
'功能:改变Excel的安全级别
'使用:Wscript,FileSystemObject,创建txt文件,注册表操作,VBS文件自我删除,改变Excel文件读写属性等
'By Keanjeason@Officefans.net
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 下可使用! ", vbOKOnly + vbCritical, "Keanjeason": 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安全级别设置失败,本程序将退出! ", vbOKOnly + vbCritical, "Keanjeason"
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
Sub SetExcelVBA()
'练习
'功能:改变Excel的安全级别
'使用:Wscript,FileSystemObject,创建txt文件,注册表操作,VBS文件自我删除,改变Excel文件读写属性等
'By Keanjeason@Officefans.net
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 下可使用! ", vbOKOnly + vbCritical, "Keanjeason": 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安全级别设置失败,本程序将退出! ", vbOKOnly + vbCritical, "Keanjeason"
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
- [VBA]用VBA宏自动改变Excel宏安全级别设置
- 用VBA宏自动改变Excel宏安全级别设…
- Excel 宏 VBA
- 【VBA】excel宏
- VBA写excel宏
- 用VBA宏代码破解Excel密码保护
- Excel用vba自动生成word
- C#调用Excel VBA宏
- VBA Excel宏常用操作
- VBA Excel宏操作示例
- excel VBA 宏 密码破解
- excel 编程VBA,宏功能
- C#调用Excel VBA宏
- Excel VBA 宏 拆分Excel表
- vba excel 单元格格式设置
- EXCEL-VBA:设置单元格格式
- 用VBA处理Excel
- VBA宏
- 可能对大家有用的工具一个
- CXO大全:
- 可能对大家有用的工具一个
- 加班!
- William H. Gates
- [VBA]用VBA宏自动改变Excel宏安全级别设置
- 注意001:@IDENTITY和scope_identity()
- SQL Server面试题
- Java2核心技术第七版的学习笔记(三) Fundamental Programming Structures in Java(Java语言的基础)(四):
- .Net平台开发的技术规范与实践精华总结
- 架构师书单
- JavaScript运行错误
- iscsi
- 下拉框的onchange事件的用法