VBS脚本控制

通过VBS操作文件比如Excel,Word,txt文件等

通过VBS给Excel文件加密

在window环境下创建一个扩展名为.vbs的文件 比如test.vbs,文件内容如下。直接双击运行test.vbs

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
Function ProtectExcel(byval filepath,byval filename)

Dim objExcel,exlSheet,exlBook,fso,exApp,rows,cols

'关闭所有excel
Set objExcel = createobject("Excel.Application")
Set fso = createobject("scripting.filesystemobject")
Set exApp = getObject(,"excel.application")

'关闭所有打开的excel文件
If TypeName(exApp) = "Application" Then
For each objBook in exApp.workbooks
msgbox objBook.FullName
objBook.close
Next
End If

Set exApp = nothing

If fso.FileExists(filepath & filename) Then
Set exlBook =objExcel.Workbooks.Open(filepath & filename)
else
Set exlBook = objExcel.Workbooks.Add '创建excel
End If

objExcel.DisplayAlerts = false

'获取所有工作表
For i = 1 To exlBook.Sheets.Count '循环所有sheet
exlBook.Sheets(i).Protect "123" '保护模式 设置密码,还可以对工作表进行其他操作,比如创建内容,设置行高等等
Next

exlBook.SaveAs(filepath & filename) 'excel另存为
'objExcel.SaveWorkspace '保存excel文件
'exlBook.close '关闭sheet页面
objExcel.Quit

Set exlSheet = nothing
Set exlBook = nothing
Set objExcel = nothing
Set fso = nothing

End Function


Call ProtectExcel("D:\","test.xlsx") '调用保护方法

参考