Quick test professional

QTP Tips QTP codes QTP Faqs and more

Search for a particular value in Excel

Set appExcel = CreateObject(“Excel.Application”)
appExcel.visible=true
 Set objWorkBook = appExcel.Workbooks.Open (filepath)’opens the sheet

 Set objSheet = appExcel.Sheets(“Sheet1”)’ To select particular sheet
With objSheet.UsedRange ‘ select the used range in particular sheet
    Set c = .Find (“nn”)’ data to find  
For each c in objSheet.UsedRange’ Loop through the used range
 If c=”nn” then’ compare with the expected data
            c.Interior.ColorIndex = 40′ make the gary color if it finds the data
End If
            Set c = .FindNext(c)’ next search
       
next
End With
objWorkBook.save
objWorkBook.close
set appExcel=nothing

Author:Mohan Kakarla

Advertisements

October 21, 2008 Posted by | Excel Automation, Uncategorized | , , | 18 Comments

Compare 2 XML files

This Example was written for comparing two XML files assuming the contents of the both XML has the following elements:
<Books>
<Title>QTP</Title>
<Author>Mohan</Author>
<Title>VBS</Title>
<Author>Kumar</Author>
</Books>

Dim description, filepath
Set xmlDoc1 = CreateObject(“Msxml2.DOMDocument”)
xmlDoc1.load(“C:Documents and Settingsmohan.kakarlaDesktop1.xml”)’file 1
Set xmlDoc2 = CreateObject(“Msxml2.DOMDocument”)
xmlDoc2.load(“C:Documents and Settingsmohan.kakarlaDesktop2.xml”)’file 2
Set ElemList1= xmlDoc1.DocumentElement.ChildNodes
Set ElemList2= xmlDoc2.DocumentElement.ChildNodes
If ElemList1.length=ElemList2.length Then’ check weather both xml file has same number of childnodes
  msgbox “Both XML files have same number of Child nodes”

   For i = 0 to ElemList1.length-1

       If ElemList1.item(i).Text=ElemList2.item(i).Text Then
          msgbox “child element:”&i &” is same in both XML files”
      Else
         msgbox “child element:”& i &” is not same in both XML files, In XML file 1, The valueis:”&ElemList1.item(i).Text &” and In XML file 1, The value

is:”&ElemList2.item(i).Text
     End If
  Next
End If

Author: Mohan Kumar Kakarla

March 3, 2008 Posted by | XML Scripting | , , , , , , , , , , | 111 Comments

Compare 2 Excel sheets cell by cell

This code will open two excel sheet and compare each sheet cell by cell, if any changes there in cells , it will highlight the cells in red color  in the first sheet.
Set objExcel = CreateObject(“Excel.Application”)
objExcel.Visible = True
Set objWorkbook1= objExcel.Workbooks.Open(“C:Documents andSettingsmohan.kakarlaDesktopDocs1.xls”)
Set objWorkbook2= objExcel.Workbooks.Open(“C:Documents and

Settingsmohan.kakarlaDesktopDocs2.xls”)

Set objWorksheet1= objWorkbook1.Worksheets(1)

Set objWorksheet2= objWorkbook2.Worksheets(1)

   For Each cell In objWorksheet1.UsedRange
       If cell.Value <> objWorksheet2.Range(cell.Address).Value Then
           cell.Interior.ColorIndex = 3’Highlights in red color if any changes in cells
       Else
           cell.Interior.ColorIndex = 0
       End If
   Next

set objExcel=nothing
 Author: Mohan Kumar Kakarla

February 27, 2008 Posted by | Excel Automation | , , , , , , , , , | 87 Comments

Excel Sorting(Ascending , Descending) By Rows and Columns

Excel Sorting By Row:

Const xlAscending = 1
Const xlNo = 2
Const xlSortRows = 2

Set objExcel = CreateObject(“Excel.Application”)
objExcel.Visible = True

Set objWorkbook = objExcel.Workbooks.Open(“C:Documents and Settingsmohan.kakarlaDesktopDocs1.xls”)
Set objWorksheet = objWorkbook.Worksheets(1)
objWorksheet.Cells(1,1).activate

Set objRange = objExcel.ActiveCell.EntireRow
objRange.Sort objRange, xlAscending, , , , , , xlNo, , , xlSortRows
set objExcel=nothing

Excel Sorting By Colum :

Const xlAscending = 1’represents the sorting type 1 for Ascending 2 for Desc
Const xlYes = 1

Set objExcel = CreateObject(“Excel.Application”)’Create the excel object
objExcel.Visible = True’Make excel visible
Set objWorkbook = _
objExcel.Workbooks.Open(“C:\Documents and Settings\mohan.kakarla\Desktop\Docs1.xls”)’Open the

document

Set objWorksheet = objWorkbook.Worksheets(1)’select the sheet based on the index .. 1,2 ,3 …
Set objRange = objWorksheet.UsedRange’which select the range of the cells has some data other than blank
Set objRange2 = objExcel.Range(“A1”)’ select the column to sort

objRange.Sort objRange2, xlAscending, , , , , , xlYes
set objExcel=nothing

Reference: MSDN

February 26, 2008 Posted by | Excel Automation | , , , , , , , , , , , , | 18 Comments

Get all files within a time range or specified time

Dim From

From = TimeSerial( 11, 0, 0 ) ‘ to emphasize 24 hour clock! 11 AM

Dim Till

Till = TimeSerial( 13, 0, 0 )

 Dim oFS

Set oFS = CreateObject( “Scripting.FileSystemObject” )

Dim oFile, tFile, Range

For Each oFile In oFS.getFolder( “.\” ).Files

tmFile = TimeValue( oFile.DateCreated )

bInRange = tFile >= From And tFile <= Till

msgbox oFile.DateCreated &”–“& oFile.Name
   Next

February 15, 2008 Posted by | Files and Folders | , , , , , , , , , , | 4 Comments

Dictonary Object

 Dictionary Object 

Dictionary Object  stores data key, item pairs. A Dictionary object stores the items in the array. Each item is associated with a unique key. The key is used to retrieve an individual item and is usually an integer or a string, but can be anything except an array.

 

 Adavntages of using it in QTP:

1. can be used as Global variable declaration. so that any test can access the values from it in the run time.

2. You can store and retrive any number of run time values in to dictonary.

3. It is one of the Parameterization techique we can use in QTP

 

 Disadvantages:

we can not specify the values in the desingn time like Datatable , Action parameters, environment variable.

So it is useful only in Run time , not design time

Methods:

Add Method

Adds a key and item pair to a Dictionary

object. object.Add (key, item)

Arguments 

object Required. Always the name of a Dictionary object.

key Required. The key associated with the item being added.item Required.

The item associated with the key being added. 

Remarks

An error occurs if the key already exists.

The following example illustrates the use of the Add method.

Dim d   ‘ Create a variable. 

Set d = CreateObject(“Scripting.Dictionary”) 

 d.Add “a”, “Athens”   ‘ Add some keys and items.  

d.Add “b”, “Belgrade”

 

 Items Method

 Returns an array containing all the items in a Dictionary object. 

object.Items( )

 Remarks The object is always the name of a Dictionary object.The following code illustrates use of the Items method:

Set d = CreateObject(“Scripting.Dictionary”)   
 d.Add “a”, “Athens”   ‘ Add some keys and items.  
  d.Add “b”, “Belgrade”    
   a = d.Items   ‘ Get the items.  
  For i = 0 To d.Count -1 ‘ Iterate the array.
       s = s & a(i) & “<BR>” ‘ Create return string.  
      Next
Msgbox s  

     Exists Method

 Returns true if a specified key exists in the Dictionary object, false if it does not.

object.Exists(key)

 Arguments

object Required. Always the name of a Dictionary object.

key Required. Key value being searched for in the Dictionary object.

 Remarks

The following example illustrates the use of the Exists method.

Set d = CreateObject(“Scripting.Dictionary”)  
  d.Add “a”, “Athens”   ‘ Add some   keys and items.   
 d.Add “b”, “Belgrade”   
   If d.Exists(“c”) Then   
    Msgbox  “Specified key exists.”   
 Else      
 Msgbox  “Specified key doesn’t exist.” 
   End If

  Keys Method

Returns an array containing all existing keys in a Dictionary object.

 object.Keys( )

Remarks

The object is always the name of a Dictionary object.

The following code illustrates use of the Keys method:   

Dim a, d, i   ‘ Create some variables.   
 Set d = CreateObject(“Scripting.Dictionary”)   
 d.Add “a”, “Athens”   ‘ Add some keys and items.  
  d.Add “b”, “Belgrade”   
  a = d.Keys   ‘ Get the keys. 
   For i = 0 To d.Count -1 ‘ Iterate the array.    
   s = s & a(i) & “<BR>” ‘ Return results.   
  Next
Msgbox s

 Remove Method  

Removes a key, item pair from a Dictionary object. 

object.Remove(key) 

Arguments

 object Required. Always the name of a Dictionary object.

key Required. Key associated with the key, item pair you want to remove from the Dictionary object.

Remarks

An error occurs if the specified key, item pair does not exist.

The following code illustrates use of the Remove method:

Dim a, d   ‘ Create some variables. 
Set d = CreateObject(“Scripting.Dictionary”) 
d.Add “a”, “Athens”   ‘ Add some keys and items. 
d.Add “b”, “Belgrade”
 d.Add “c”, “Cairo”  
d.Remove(“b”)   ‘ Remove second pair.

RemoveAll Method

 The RemoveAll method removes all key, item pairs from a Dictionary object.

 object.RemoveAll( ) 

Dim a, d, i    Create some variables.
 Set d = CreateObject(“Scripting.Dictionary”) 
d.Add “a”, “Athens”   ‘ Add some keys and items.
 d.Add “b”, “Belgrade” 
d.Add “c”, “Cairo”  
a = d.RemoveAll   ‘ Clear the dictionary.

Author: Mohan Kakarla

Reference:MSDN

February 15, 2008 Posted by | Dictonary Object | , , , , , , , , , | 1 Comment

Get The Latest Created File

   Const cdtFirst = #1/1/100#

   Dim sFolder  : sFolder    = “.\”
   Dim oFS      : Set oFS    = CreateObject( “Scripting.FileSystemObject” )
   Dim dtLatest : dtLatest   = cdtFirst
   Dim sExt     : sExt       = “ppt” ‘ not entirely sure about this extension
   Dim oLFile   : Set oLFile = Nothing
   Dim oFile
   For Each oFile in oFS.GetFolder( sFolder ).Files
       If sExt = LCase( oFS.GetExtensionName( oFile.Name ) ) Then
          If dtLatest < oFile.DateLastAccessed Then ‘ maybe DateCreated/DateLastModified
             dtLatest = oFile.DateLastAccessed
             Set oLFile = oFile
          End If
       End If
   Next
   If oLFile Is Nothing Then
      WScript.Echo “No file with extension”, sExt, “found.”
   Else
      WScript.Echo “found”, oLFile.Name, oLFile.DateLastAccessed
   End If

February 15, 2008 Posted by | Files and Folders | , , , , , , , , , , | Leave a comment

How to register COM with QTP?

How to register COM with QTP? 

Steps to create Intellisense in QTP:

1. Create a wsc(windows scripting component) file in the below given format:

<?xml version=”1.0″?>
<component>

<?component error=”true” debug=”true”?>
<reference object=”Scripting.Dictionary” />
<registration description=”Functions” progid=”QTP.Functions” version=”1.00″ classid=”{d7c40957-00d4-41a5-a87b-89d1db311423}”>
</registration>

<public>

<method name=”Function1″ internalName=”Functiona1″ dispid=”0″>
<PARAMETER name=”strFileName”/>
</method>
<method name=”Function2″>
</method>
<method name=”Function3″ >
</method>
</public>

</component>
2. After the file is created, register the same using regsvr32.
3. Now create the following registry entries in the path HKCU\Software\Mercury Ineteractive\QuickTest Professional\MicTest\Reserved Objects\Functions –

ProgID REG_SZ QTP.Functions
UIName REG_SZ Functions

4. Restart QTP.

  Source: Mercury Forum’s KB articles

February 14, 2008 Posted by | COM and QTP | , , , , , , , , | 3 Comments

Get Each Radio in RadioGroup And Check

Get Each Radio in RadioGroup

And Check

Set opt=Description.Create
opt(“html tag”).value=”INPUT”
opt(“type”).value = “radio”
Dim allOptions,all
Set allOptions=Browser(“a”).Page(“b”).Frame(“c”).ChildObjects(opt)
all=allOptions.count
MsgBox all

for i= 0 to all
eName=allOptions(i).getROProperty(“name”)
noofradio=allOptions(i).getElementsByName(eName).length
for j= 0 to noofradio-1
radioname=allOptions(i).object.document.getElementsByName(eName).item(j).GetAdjucentText(“afterend”)allOptions(i).object.document.getElementsByName(eName).item(j).checked=true
msgbox radioname
next
next

  Source: Mercury Forum’s KB articles

February 14, 2008 Posted by | Descriptive Programming and Child Objetcs | , , , , , , , , | 8 Comments

How To close MsgBox Runtime

 

How To close MsgBox Runtime 

Write the below line as the first-line in your script. Set sh = CreateObject(“Scripting.Shell”) …This will create a shell object at run-time and will be available until the test ends.

If you are getting problem like ,Activex Component can’t create object Then use  Set sh =CreateObject(“WScript.Shell”)

 Now… where ever you are having “msgbox” replace it with..

sh.Popup “message content”, 5, “msg header”, (0+4)

 the above line will show the message content for 5 seconds(and vanishes after that).

The alert-box’s header will have “header info”. Last parameter(0+4) is actually two things. 0 -> show OK button in the alertBox 48 -> “!” icon… if you use 64 u will see X icon instead BTW… since u need the message to be displayed

 

 

February 14, 2008 Posted by | QTP Tips and Faqs | , , , , , , , , | 13 Comments