Blog to understand automation concepts in QTP, Selenium Webdriver and Manual Testing concepts
QTP VBScript - Excel Application - Sorting data in excel worksheet based on column name
Labels:
QTP,
qtp Scripts,
Test Scripts,
VBScript,
VBScript Tutorials,
Working with excel
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
Labels:
Descriptive,
QTP,
qtp Scripts,
Test Scripts
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
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
Subscribe to:
Posts (Atom)