上一章我们讲解了很多vbs基础知识,这章我们看一下vbs较高级的应用,主要内容如下:
◆ 文本文件的读写
◆ excel文件的读写
◆ xml文件的读写
◆ 连接数据库
一、文本文件的读写
Function write_txt(txt_path)
Const ForReading =1,ForWriting = 2,ForAppand = 8
Dim fso,file
Set fso = CreateObject("Scripting.fileSystemObject")
If fso.FileExists(txt_path) Then
Set file = fso.OpenTextFile(txt_path,ForAppand)
file.Write("new write content!" & vbLf & "china!")
Else
MsgBox txt_path & " is't exists!"
End If
Set fso = Nothing
Set file = nothing
End function
Function read_txt(txt_path)
Const ForReading =1,ForWriting = 2,ForAppand = 8
Dim fso,file,msg
Set fso = CreateObject("Scripting.fileSystemObject")
If fso.FileExists(txt_path) Then
Set file = fso.OpenTextFile(txt_path,ForReading)
Do While (Not file.AtEndOfLine)
msg = msg & vbLf & file.ReadLine
loop
Else
MsgBox txt_path & " is't exists!"
End If
MsgBox txt_path & "中的内容为:" & vbLf & msg & vblf
Set fso = Nothing
Set file = Nothing
End Function
write_txt("d:\zj.txt")
read_txt("d:\zj.txt")
二、excel文件的读写
Function xls(xls_path)
Dim excel,fso
Set excel = CreateObject("Excel.Application")
Set fso = CreateObject("Scripting.fileSystemObject")
If fso.FileExists(xls_path) Then
excel.Workbooks.open(xls_path)
excel.Worksheets("sheet1").activate
excel.Cells(1,1) = "china"
excel.ActiveWorkbook.save()
excel.quit
Else
MsgBox xls_path & " is't exists!"
End If
Set fso = nothing
End Function
xls("d:\zj.xls")
三、xml文件的读写
'定义
Class clsGetProfile
Private rootDoc
Public Sub setProfile(strFileName)
Set data_xml = CreateObject("Microsoft.XMLDOM")
data_xml.async = False
data_xml.load(strFileName)
Set rootDoc = data_xml.documentElement
End Sub
Public Function getItem(strSectionName, itemName)
Set sectionNode = rootDoc.selectSingleNode(strSectionName)
getItem = sectionNode.selectSingleNode(itemName).attributes(0).nodeValue
WScript.Echo getItem
End Function
End Class
'调用
Dim config
Set config = New clsGetProfile
Call config.setProfile("Config.xml")
Call config.getItem("OtherSection", "host")
Call config.getItem("OtherSection", "user")
Call config.getItem("WindowsLogToolConfig", "host")
Call config.getItem("WindowsLogToolConfig", "port")
'备注: Config.xml文件内容如下
<?xml version="1.0" encoding="UTF-8"?>
<Configuration>
<OtherSection>
<host value="192.168.0.241"/>
<user value="root"/>
</OtherSection>
<WindowsLogToolConfig>
<host value="192.168.0.188"/>
<port value="3306"/>
</WindowsLogToolConfig>
</Configuration>
四、连接数据库
步骤一:确定数据库连接字符串
对于MS sql server数据库的连接,我们首先要知道所用的MS sql server数据库的连接字符串.方法如下:
1、新建.txt文件,修改文件名(包括后缀)为XX.udl,双击打开XX.udl文件
2、在"Provider"选项中选择"Microsoft OLE DB Provider for SQL Server",在"Connection"中
3、选择相应的server name和database,有用户名和密码的输入用户名和密码,点击右下方的"Test Connection"
4、验证数据库是否连通.点击"OK"
5、把XX.udl文件用记事本方式打开,以"Provider"开始的部分就是你的数据库连接字符串.
步骤二:编写代码
'1、连接数据库 Dim Cnn Dim Rst Dim strCnn strCnn ="Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Data Source=zj-27806" Set Cnn = CreateObject("ADODB.Connection") Cnn.Open strCnn Set Rst = CreateObject("ADODB.Recordset") Rst.Open "use factory ",Cnn Rst.Open "select distinct 姓名 from worker ",Cnn '2、读取数据库 Dim i i=1 while not Rst.EOF CompetencieName=Rst("姓名").value '姓名字段的值 msgbox CompetencieName Rst.MoveNext Wend '3、关闭数据库 Cnn.close '4、清除对象 Set con=nothing Set fso = nothing '5、备注: '数据库factory中的表worker的姓名字段有"china","usa","brazil","usa" '最后输出:"china","usa","brazil"