VBS脚本控制

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

常用函数

  • Abs(number) 返回一个数的绝对值
  • Mid(string,start,length) 从string字符串的start字符开始取得length长度的字符串,如果省略第三个参数表示从start字符开始到字符串结尾的字符串
  • Left(string,length) 从string字符串的左边取length长度的字符串
  • LCase(string) 将字符串里的所有大写字母转化成小写字母
  • InStr(string1,string2) 回string2字符串在string1字符串中第一次出现的位置

通过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") '调用保护方法

VBS正则表达式使用

*.+是贪婪的,.?是非贪婪版本

1
2
3
4
5
6
7
8
9
Dim cert_str, re, ms, m
cert_str = "-----BEGIN CERTIFICATE----- sfsdfasdfsd 123 dfsdfsdfsdfsad 123 -----END CERTIFICATE----------BEGIN CERTIFICATE-----23-----END CERTIFICATE-----"
Set re = New RegExp
re.Global = True
re.Pattern = "(-----BEGIN CERTIFICATE-----([\s\S].*?)(-----END CERTIFICATE-----))" '([\s\S]*?) ([\s\S].*?) ([\s\S]*)
Set ms = re.Execute(cert_str)
For Each m In ms
WScript.Echo m
Next

读取txt证书到excel

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
' 读取证书文件txt写入Excel表格,文件类ANSI,excel文件名与目录名不要出现中文
Set fso = createobject("scripting.filesystemobject")
Set oExcel = CreateObject( "Excel.Application" )

Set file=fso.opentextfile("test.txt") '读取的文件
oExcel.WorkBooks.Open("G:\test2\test.xls") '保存的目标文件

'oExcel.Cells(1,4).Value = "第一行第三列"
ts = file.readall
file.close '读取后关闭文件
strarr=split(ts,vbcrlf)
Dim count
Dim re '正则表达式
count = 3
for i=0 to ubound(strarr)

Set re = New RegExp
re.Global = True
re.Pattern = "(-----BEGIN CERTIFICATE-----([\s\S]*)-----END CERTIFICATE-----)"
Set ms = re.Execute(strarr(i))
For Each m In ms
'WScript.Echo m '打印日志
s = Replace(m,"-----BEGIN CERTIFICATE-----"&chr(10),"")
t = Left(s,InStr(s,"-----END CERTIFICATE-----")-2) '多-1 剔除一个换行符
'WScript.Echo t
oExcel.Cells(count,12).Value = t
count = count+1
Next

Next
oExcel.ActiveWorkBook.Saved = False '操作的是同一个Excel文件,需要另存为其他Excel文件
oExcel.ActiveWorkBook.Close
oExcel.WorkBooks.Close
oExcel.Quit

参考