三、VBS高级应用

        上一章我们讲解了很多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"