我常用的VBS方法(QTP)

这些是4年前在HP用QTP做自动化测试时候总结的一些，现在贴出来，说不准以后会不会用到

当初花了2天时间写的一个自动生成的Excel Report

Public Function Report (status, objtype, text)
         Dim TestName

         Reporter.Filter = rtEnableAll
         Reporter.ReportEvent status, objtype, text
         Reporter.Filter = rfDisableAll

        Call WExcel(status,objtype,text)

End Function

Function CreateExcel(sFolderPath)
    Dim cTestName_Sum,cStatus_Sum,cSum_Sum,cPass_Sum,cFail_Sum,cTime_Sum
       Dim cTestName,cStep,cStatus,cDetail,cTime,cPicName
    Dim oFile,oExcel,sExcelPath,sNewBook,sNewSheet

    cTestName_Sum = 1
    cStatus_Sum = 2
    cSum_Sum = 3
    cPass_Sum = 4
    cFail_Sum = 5
    cTime_Sum = 6

    cTestName = 1
    cStep = 2
    cStatus = 3
    cDetail = 4
    cTime = 5
    cPicName = 6
    Set oFile = CreateObject("Scripting.FileSystemObject")
    Set oExcel = CreateObject("Excel.Application")
    oExcel.Visible  =   False
    
    If not oFile.FolderExists(sFolderPath) Then
        oFile.CreateFolder(sFolderPath)
    End If

    sExcelPath = sFolderPath&"/Result.xls"

    If not oFile.FileExists(sExcelPath) Then
        Set sNewBook = oExcel.Workbooks.Add
        With sNewBook.Worksheets(1)

        End With
        With sNewBook.Worksheets(1)
        .Activate
        .Cells(1,cTestName_Sum).value = "TestName"
        .Cells(1,cStatus_Sum).value = "Status"
        .Cells(1,cSum_Sum).value = "Sum Num"
        .Cells(1,cPass_Sum).value = "Passed Num"
        .Cells(1,cFail_Sum).value = "Failed Num"
        .Cells(1,cTime_Sum).value = "TestTime"
        .Name = "Summary"
        .Rows(1).Font.Bold = True
        .Columns(cTestName_Sum).ColumnWidth= 25
         .Columns(cStatus_Sum).ColumnWidth= 10
        .Columns(cSum_Sum).ColumnWidth= 11
        .Columns(cPass_Sum).ColumnWidth= 11
        .Columns(cFail_Sum).ColumnWidth= 11
        .Columns(cTime_Sum).ColumnWidth= 15
        End With
        With sNewBook.Worksheets(2)
        .Activate
        .Cells(1,cTestName).value = "TestName"
        .Cells(1,cStep).value = "Step Object"
        .Cells(1,cStatus).value = "Status"
        .Cells(1,cDetail).value = "Result Detail"
        .Cells(1,cTime).value = "TestTime"
'        .Cells(1,cPicName).value = "Capture Screen Name"
        .Name = "Passed Step"
        .Rows(1).Font.Bold = True
        .Columns(cTestName).ColumnWidth= 25
        .Columns(cStep).ColumnWidth= 40
        .Columns(cStatus).ColumnWidth= 8
          .Columns(cDetail).ColumnWidth= 50
        .Columns(cTime).ColumnWidth= 15
        .Columns(cPicName).ColumnWidth= 40
        End With
        With sNewBook.Worksheets(3)
        .Activate
        .Cells(1,cTestName).value = "TestName"
        .Cells(1,cStep).value = "Step Object"
        .Cells(1,cStatus).value = "Status"
        .Cells(1,cDetail).value = "Result Detail"
        .Cells(1,cTime).value = "TestTime"
        .Cells(1,cPicName).value = "Capture Screen Name"
        .Name = "Failed Step"
        .Rows(1).Font.Bold = True
        .Columns(cTestName).ColumnWidth= 25
        .Columns(cStep).ColumnWidth= 40
        .Columns(cStatus).ColumnWidth= 8
        .Columns(cDetail).ColumnWidth= 50
        .Columns(cTime).ColumnWidth= 15
        .Columns(cPicName).ColumnWidth= 40
        End With
        sNewBook.SaveAs sExcelPath
        oExcel.Application.quit
        Set sNewBook = Nothing
'        CreateExcel = sExcelPath
    End If

End Function

Function WExcel(Status,sStep,sDetail)
    Dim cTestName_Sum,cStatus_Sum,cSum_Sum,cPass_Sum,cFail_Sum,cTime_Sum
       Dim cTestName,cStep,cStatus,cDetail,cTime,cPicName
    Dim oFile,oExcel,sExcelPath,sNewBook,sNewSheet
    Dim iLen,iLenPass,iLenFail,sTestName,sFolderPath
    sTestName = Environment.Value("TestName")
    sFolderPath = "C:/FP_Results"

    cTestName_Sum = 1
    cStatus_Sum = 2
    cSum_Sum = 3
    cPass_Sum = 4
    cFail_Sum = 5
    cTime_Sum = 6

    cTestName = 1
    cStep = 2
    cStatus = 3
    cDetail = 4
    cTime = 5
    cPicName = 6

    CreateExcel(sFolderPath)
'    msgbox sExcelPath

    Set oFile = CreateObject("Scripting.FileSystemObject")
    Set oExcel = CreateObject("Excel.Application")
    oExcel.Visible  =   False
    sExcelPath = sFolderPath&"/result.xls"

    Set sNewBook = oExcel.Workbooks.Open(sExcelPath)
    Set sNewSheet = sNewBook.Worksheets(1)
    Set sNewSheetPass = sNewBook.Worksheets(2)
    Set sNewSheetFail = sNewBook.Worksheets(3)
    iLen = sNewSheet.UsedRange.Rows.count
    iLenPass = sNewSheetPass.UsedRange.Rows.count
    iLenFail = sNewSheetFail.UsedRange.Rows.count

    If Status = 0 Then
        With sNewSheetPass
            .Activate
            .Cells(iLenPass+1,cTestName).value = sTestName
            .Cells(iLenPass+1,cStep).value = sStep
            .Cells(iLenPass+1,cDetail).value = sDetail
            .Cells(iLenPass+1,cTime).value = now
            .Cells(iLenPass+1,cStatus).value = "Passed"
            .Cells(iLenPass+1,cStatus).Font.Color = vbGreen
            .Cells(iLenPass+1,cStatus).Font.Bold = True
        End With
        With sNewSheet
        .Activate
        If  sNewSheet.Cells(iLen,cTestName_Sum).value = sTestName Then
            .Cells(iLen,cSum_Sum).value = .Cells(iLen,cSum_Sum).value+1
            .Cells(iLen,cPass_Sum).value = .Cells(iLen,cPass_Sum).value+1
        Else
            .Cells(iLen+1,cTestName_Sum).value = sTestName
            .Cells(iLen+1,cSum_Sum).value = 1
            .Cells(iLen+1,cTime_Sum).value =now
            .Cells(iLen+1,cPass_Sum).value = 1
            .Cells(iLen+1,cFail_Sum).value = 0
            .Cells(iLen+1,cStatus_Sum).value = "Passed"
            .Cells(iLen+1,cStatus_Sum).Font.Color = vbGreen
            .Cells(iLen+1,cStatus_Sum).Font.Bold = True
        End If
        End With
    Else
        With sNewSheetFail
            .Activate
            .Cells(iLenFail+1,cTestName).value = sTestName
            .Cells(iLenFail+1,cStep).value = sStep
            .Cells(iLenFail+1,cDetail).value = sDetail
            .Cells(iLenFail+1,cTime).value = now
            .Cells(iLenFail+1,cStatus).value = "Failed"
            .Cells(iLenFail+1,cStatus).Font.Color = vbRed
            .Cells(iLenFail+1,cStatus).Font.Bold = True
'            oExcel.Application.Visible = False
            .Cells(iLenFail+1,cPicName).value = CapturePic(sFolderPath,sStep)
Call   .Hyperlinks.Add(.Cells(iLenFail+1,cPicName),sFolderPath&"/"&.Cells(iLenFail+1,cPicName).value,"","Capture screen when failed")
        End With
        With sNewSheet
        .Activate
        If  sNewSheet.Cells(iLen,cTestName_Sum).value = sTestName Then
            .Cells(iLen,cSum_Sum).value = .Cells(iLen,cSum_Sum).value+1
            .Cells(iLen,cFail_Sum).value = .Cells(iLen,cFail_Sum).value+1
            .Cells(iLen,cStatus_Sum).value = "Failed"
            .Cells(iLen,cStatus_Sum).Font.Color = vbRed
            .Cells(iLen,cStatus_Sum).Font.Bold = True
        Else
            .Cells(iLen+1,cTestName_Sum).value = sTestName
            .Cells(iLen+1,cSum_Sum).value = 1
            .Cells(iLen+1,cTime_Sum).value =now
            .Cells(iLen+1,cPass_Sum).value = 0
            .Cells(iLen+1,cFail_Sum).value = 1
            .Cells(iLen+1,cStatus_Sum).value = "Failed"
            .Cells(iLen+1,cStatus_Sum).Font.Color = vbRed
            .Cells(iLen+1,cStatus_Sum).Font.Bold = True
        end if
        End With
    End If

    sNewBook.Save
    oExcel.Application.Quit
    Set sNewBook = Nothing
    Set oExcel = Nothing

End Function

Public Function CapturePic(pathway,sStep)
  Dim datestamp
  Dim picName
  Dim filename
  Dim ofile,ran
  datestamp = Hour(Now)&Minute(Now)&Second(Now)
  Set  ofile  =   CreateObject("Scripting.FileSystemObject")
  Randomize 
  ran = Int(Rnd()*100)
  filename = Environment("TestName")&"_"&sStep&datestamp&ran
  filename = Replace(filename,"|","")
  filename = Replace(filename,">","")
  filename = Replace(filename,"<","")
  filename = Replace(filename,"?","")
  filename = Replace(filename,"*","")
  filename = Replace(filename,"","")
  filename = Replace(filename,"/","")
  filename = Replace(filename,":","")
  If ofile.FileExists(pathway+"/"+""&filename&".png") Then
      filename=filename&"1"
  End If
  filename = filename&".png"
  picName = filename
  filename = pathway + "/" + ""&filename
  Desktop.CaptureBitmap filename
  CapturePic = picName
End Function

With Object

With Browser("DUI 02").Page("DUI 02").SlvWindow("Shell").SlvDialog("FileFlightFormView")

    iTimer=Timer
     Do
     Loop until .Exist  or (Timer-iTimer)>500
      If .Exist Then
     Reporter.ReportEvent micPass,"The system displays the fill form","dialog box displays successfully"
     .SlvButton("Select").Click
    end if

ArrayList Sort

Option Explicit
Dim mArray()
ReDim mArray(10)

mArray(0)="0AABB"
mArray(1)="11abc"
mArray(2)="2ec11"
mArray(3)="aAACC"
mArray(4)="aAACC"
mArray(5)="aaaxx"
mArray(6)="AAAyb"
mArray(7)="AAdew"
mArray(8)="aaxew"
mArray(9)="ddddd"
mArray(10)="zzaAA1"

' Call function to check the order of the array
Call IsSorted(mArray)

Function IsSorted(arraylist)
 Dim leng,i

  'get the length of the array
 leng=Ubound(arraylist)+1

  'check whether arraylist  length is more than two
   If leng < 2Then
       msgbox("No enough data in this arraylist.")
   End If
 For i=0 to Ubound(arraylist)-1

'The StrComp function compares two strings and returns a value that represents the result of the comparison.
'0 = vbBinaryCompare - Perform a binary comparison,1 = vbTextCompare - Perform a textual comparison 

     If strcomp(arraylist(i),arraylist(i+1),1) = 1 Then
'         call Report (micFail, "Check the Sort of the array", "The array sort is not correct between "&arraylist(i)& " and "&arraylist(i+1)& " .")
         msgbox "The array sort is not correct between "&arraylist(i)& " and "&arraylist(i+1)& " ."
         Exit function
     End If
 Next
end function

Send Key

Set WshShell = WScript.CreateObject("WScript.Shell")
WshShell.Run "notepad"
WScript.Sleep 500
WshShell.AppActivate "Notepad"
Wshshell.SendKeys "%(123)"

Set shell=Createobject("WScript.Shell")
shell.SendKeys "{END}"

Run Action

RunAction "login [login_search]", oneIteration, , , url
RunAction "Search_Flight [login_search]", oneIteration, flight, "", ""

正则表达式

Dim itype
itype="^(3[0-1]|2[0-9]|1[0-9]|0[1-9])-(Jan|Feb|Mar|Apr|May|Jun|Jul|Aug|Sep|Oct|Nov|Dec)-d{2}/[0-6]d:[0-6]d$"
msgbox isPatternMatch(itype,"30-Jan-04/00:00")

Public Function isPatternMatch(patternType,inputData)
  Dim myReg
  If trim(inputData) = "" Then
      isPatternMatch = true
      Exit function
  End If
  Set myReg= New RegExp 
 'Set  pattern
  myReg.Pattern =patternType
' Set case insensitivity
  myReg.IgnoreCase = True 
' Set global applicability
  myReg.Global = True 
' Execute search
  isPatternMatch=myReg.test(inputData)
End Function

PageScrollDown

browser("title:="&PageTitleURL).Page("title:="&PageTitleURL).object.body.doScroll("scrollbarPageDown")

Import from Excel

'datatable.ImportSheet "C:FPdatatableInput _ Initial018.xls" ,1 ,"Global" 
rowcount  = DataTable.GetSheet("Global").GetRowCount 
'msgbox "step1:the number need to check is: "&rowcount

Get Value from Table

'Get value
value=datatable.getsheet("sheet").getparameter("Para").valueByrow(1)
value=slvTable("table).getcelldata(1,"para")
'Get Rowcount
count=datatable.getsheet("sheet").getrowcount
count=slvTable("table").rowcount

Connect Oracle

Dim Cnn
Set Cnn = CreateObject("ADODB.Connection")
Cnn.ConnectionString ="Provider=OraOLEDB.Oracle.1;Password=skyobj;Persist Security Info=True;User ID=skyobj;Data Source=TAEDFLP.airservices.eds.com"
Cnn.Open
If (Cnn.State = 0 )Then
MsgBox "failed"
'    Call Report(micFail, "Database connect testing", "Failed!")
'Reporter.ReportEvent micFail, "Database connect testing", "连接数据库失败"
Else
MsgBox "success"
'   Call Report(micPass, "Database connect testing",   "Success!")
'Reporter.ReportEvent micPass, "Database connect testing",   "连接数据库成功"
end if

CheckDate

Public Function currentdate()
    a = day(date)
    b = MonthName(month(date),true)
    c = right(Year(date),2)
    if cint(a) <10 then a = "0"&a
    currentdate  = a&"-"&b&"-"&c
End Function

MsgBox currentdate()

Click Save button （FP,silverligh)

Set var_Object = Browser("FPC").Page("FPC").Object.body
    var_Object.doScroll("pageDown")
    x = Browser("FPC").Page("FPC").SlvWindow("Shell").SlvButton("btnSave").GetROProperty("x") + 10
    y = Browser("FPC").Page("FPC").SlvWindow("Shell").SlvButton("btnSave").GetROProperty("y") + 10
    Browser("FPC").Page("FPC").WinObject("MicrosoftSilverlight").Click x,y