QTP VBScript - Excel Application - Sorting data in excel worksheet based on column name

Below script or code snippet is very useful in sorting an excel workbook based on the name of columns header . We can sort multiple columns of the excel worksheet. For e.g there are 3 columns with header name as "name","class" and "value" based on which we want to sort the database with priority in order name>class>value. So provide strSortbyfield as "name>class>value". Below piecee of code can be implemented to achieve the same

strWorkBook = InputBox ("Input the workbook with full path")

strWorkSheet = InputBox("Enter the sheet name")

strSortbyFields = Inputbox ("provide the field names, seperated by > in case of multiple sorting of data is required")

Set objExcel = Createobject("Excel.Application")

objExcel.Visible = False

Set XLWorkBook = objExcel.WorkBooks.Open(strWorkbook)

Set objWorksheet = XLWorkBook.Worksheets(strWorkSheet)  

Set objRange = objWorksheet.UsedRange

ColCount = objRange.columns.count

strSortDataArr = split(strSortbyFields,">")

intCnt = ubound(strSortDataArr)

For i = intCnt to 0 step -1

For j = 1 to ColCount step 1

If (objWorkSheet.cells(1,j).value =strSortDataArr(i)) Then

''get the column based on which data needs to be sorted in the excel document

chrCde = Chr(asc("A")- 1+j) & "1"

boolExcelSortData = True

Set objRangeSrt = objExcel.Range(chrCde)

objRange.Sort objRangeSrt, xlDescending, , , , , , xlYes 

XLWorkBook.save

             Exit For

End If  

Next

Next

''save the workbook and close

XLWorkBook.Save

XLWorkBook.Close

objExcel.Quit

Set XLwORKBook = Nothing

Set objExcel = Nothing

How to use GETROProperty to extract value using descriptive programming in QTP

We can use descriptive programming to display the value stored for object e.g the value displayed in a edit box or a webelement or default value in a webList.

We can learn following key points from the code below:

• How to create a description object using description.create

• How to implement descriptive programming for object uniquely identified by using multiple properties

• How to use getROProperty to extract information at runtime

• How to return value by a function.

Public Function ValueDisplayedForObject(DescObj,ObjType)
On error resume next
Set objDesc= Description.create
'Set of Property value are delimited by ,
If (instr(1,DescObj,",") >0) Then
   ''Split multiple links based on delimiter. ,
arrDesc = split(DescObj,",")
intPropSet = Ubound(arrDesc)
''Loop through each of the link and click the required link
for i = 0 to intPropSet
'' Property and value for property are seperated by |
strDesc = split(arrDesc(i),"|")
objDesc(strDesc(0)).value = strDesc(1)
Next
''In case of only one property value set of image
Else
strDesc = split(DescObj,"|")
objDesc(strDesc(0)).value = strDesc(1)
End If
If (ucase(ObjType) = "WEBELEMENT")  Then
ValueDisplayedForObject = Browser("Browser_Encompass").Page("title:=.*").WebElement(objdesc).GetRoProperty("innertext")
ElseIf (ucase(ObjType) = "WEBLIST") Then
ValueDisplayedForObject = Browser("Browser_Encompass").Page("title:=.*").WebList(objdesc).GetRoProperty("value")
Else
ValueDisplayedForObject = Browser("Browser_Encompass").Page("title:=.*").WebEdit(objdesc).GetRoProperty("value")
End If
If (err.number<>0) Then
Reporter.ReportEvent micpass,"Value displayed for object",err.description
End If 
End Function.

 Please see below links for more on descriptive programming

qtp descriptive programming basics

descriptive programming examples and comparison with OR

Script to execute specific QTP tests from Test Set in Quality Center using VBScript

Below code will execute tests in a test set in QC based on name of the test set. The code can run all the scripts in test set, only passed test script, only failed test script. This can be manipulated further based on requirement. Also an html file with test status will be displayed once test set execution is completed.

 UserName = InputBox("Enter the user name for QC Login", "QC UserName")  
 Password = InputBox("Enter the password for QC Login", "QC Password")  
 strExecFlag = InputBox("Enter the option for test execution" & vbcrlf & "Valid Values - P, A,F" & vbcrlf & "A: Run All test Scripts"& vbcrlf & "F: ExecuTe script not passed Only"& vbcrlf & "P: Execute Passed Test Scripts","Test Execution Option")  
 'Return the TDConnection object.  
 Set QCConnection = CreateObject("TDApiOle80.TDConnection")  
 QCConnection.InitConnectionEx QCurl  
 QCConnection.login UserName, Password  
 QCConnection.Connect DomainName, ProjectName  
 Set TSetFact = QCConnection.TestSetFactory   
 Set tsTreeMgr = QCConnection.TestSetTreeManager   
 Dim qtApp 'As QuickTest.Application ' Declare the Application object variable   
 Dim qtLibraries 'As QuickTest.TestLibraries ' Declare a test's libraries collection variable   
 Set tsFolder = tsTreeMgr.NodeByPath(<QC Test Set Path>) ' test set starts with "Root"  
 Set tsList = tsFolder.FindTestSets(<TestSetName>) 'Testset name  
 strTable = ""  
 Set theTestSet = tsList.Item(1)  
 For Testset_Count=1 to tsList.Count  
    Set theTestSet = tsList.Item(Testset_Count)   
    Set TSTestFact = theTestSet.TSTestFactory  
    TSName = theTestSet.Name  
    Set TestSetTestsList = TSTestFact.NewList("")   
   For Each theTSTest In TestSetTestsList   
      TestName = theTSTest.Test.Name    
      TestScript = <Test Script path in QC> & TestName    
      TestStatus = theTSTest.Status  
      If ((TestStatus ="Passed" and ucase(strExecFlag) = "P") OR (TestStatus <>"Passed" and ucase(strExecFlag) = "F") or (ucase(strExecFlag) = "A")) Then  
         Set qtApp = CreateObject("QuickTest.Application")   
         If qtApp.launched <> True then   
             qtApp.Launch   
         End If  
        qtApp.Visible = "true"  
       If Not qtApp.TDConnection.IsConnected Then  
           qtApp.TDConnection.Connect QCurl,DomainName,ProjectName,UserName,Password,False  
       End If  
  ' Create the Application object  
      qtApp.Open TestScript, True  ' Open the test in read-only mode  
      Set qtTest = qtApp.Test  
     Set qtResultsOpt = CreateObject("QuickTest.RunResultsOptions")   
     ' Create the Run Results Options object   
     ' New Run Results in Quality Center option  
     qtResultsOpt.TDTestInstance = 1  
     qtResultsOpt.TDRunName= "Run_" & Month(Now) & "-" & Day(Now) & "_" & Hour(Now) & "-" & Minute(Now) & "-" & Second(Now)  
    qtResultsOpt.TDTestSet = TestSetPath ' Path to the Test Set where we should save the results  
    Set fso=createobject("Scripting.FileSystemObject")   
    If fso.FolderExists("C:\Res1") Then   
         fso.DeleteFolder("C:\Res1")  
    End If  
    qtResultsOpt.ResultsLocation = "C:\Res1"  
    qtTest.Run qtResultsOpt,True  
    TestStatus = qtTest.LastRunResults.Status  
    qtTest.Close  
    qtApp.quit  
    Set qtApp = Nothing    strTable = strTable & "<tr><td>"&TestName&"</td><td>"&TestStatus&"</td></tr>"  
    End If   
   Next  
 Next  
 Set objFSO = CreateObject("scripting.filesystemObject")  
 If (objFSO.FolderExists("c:\Temp") = False) Then  
    objFSO.CreateFolder("c:\Temp")  
 End If  
 strTable = "<html><h1>Test Results<h1><table border =""""2""""<tr><td>TestCaseName</td><td>Test Execution Status</td></tr>" & strTable &"</table></html>"  
 Set objFl = objFSO.CreateTextFile("c:\Temp\Test.html")  
 objFl.Write strTable  
 Set objFl = nothing  
 CreateObject("WScript.Shell").Run "c:\Temp\Test.html"