Example to explain working with excel application in QTP using VBScript

 In this post, we will know how to work with excel application with an example . We will explain how to copy a source file and create an destination file with same name or a dynamic name generated. We will also explain how to copy data from an excel worksheet to another worksheet, delete an excel sheet, and rename an excel worksheet using excel application object.

 The problem we will resolve using this code is as follows. 

In the below code, we will explain how to implement the above using vb script.

destfile = CopyFile("D:\testt\TestSupport_1.xls", "D:\testt\", 1)

copydata "D:\testt\generatedRpt.xls", destfile, "general_report", "temp"

masterfile = Copyfile(destfile, "D:\testt\TestingSupport_Daily_Data.xls",0)

'' Description - This function will copy the file from source location to destination folder. In this example we have used below three parameter as per our need.

''Source file - Exact name and location of the source file. This is an external file generated at a fixed location which was first part of problem and will be handled using QTP

''DestinationFolder - this is either the folder location with or without file name. 

''filename - this shows filename is included in the the destinationfolder. In case of dynamic naming of the file, provide value as 1 else provide value as 0.

function CopyFile(SourceFile, DestinationFolder, filename)

if (filename =1) then

'' generate a dynamic name for the file in case filename argument is provided as 1

    DestinationFile = DestinationFolder + "TestSupport" + cstr(DatePart("m",Now()))+ cstr(DatePart("d",Now()))+ cstr(DatePart("yyyy",Now())) + ".xls"

else

   DestinationFile = Destinationfolder

end If

    Set fso = CreateObject("Scripting.FileSystemObject")

    'Check to see if the file already exists in the destination folder

    If fso.FileExists(DestinationFile) Then

    ''In case of destinationfile already exists, delete the existing file

fso.DeleteFile DestinationFile, True

    End If

'' Copy the file from source location to destination location

    fso.CopyFile SourceFile, DestinationFile, True

''close the instance of filesystem object

    Set fso = Nothing

''return the value of file name as the rsturn value of the function.

copyfile = Destinationfile

End function

'

'' Description - this function will copy data from sheet1 of workbook1 to sheet2 of workbook2.

sub copydata(workbook1, workbook2, sheet1,sheet2)

'' Create an instance of excel object

Set objExcel = CreateObject("Excel.Application") 

'' define if the excel needs to be displayed and alerts to be displayed. here the excel will be visible but no alerts will be displayed in case of alert prompt in the excel application.

objExcel.Visible = True

objExcel.DisplayAlerts = False

'' create an instance of excel workbooks for source as well as destination workbook

Set objsrcFile= objExcel.Workbooks.Open(workbook1)   

Set objdestfile = objExcel.Workbooks.Open(workbook2) 

objsrcfile.Activate

'' create instance of worksheet in the workbook

Set objsrcSheet = objsrcfile.Worksheets(sheet1)

Set objdestSheet = objdestfile.Worksheets(sheet2)

'' gather the data from the source file to destination file

'' In the below example , we are copying the data from 4th row in source file to destination file. we can start from first row also if required.

icolcount = objsrcSheet.usedrange.columns.count

irowcount = objsrcSheet.usedrange.rows.count

for i = 4 to irowcount-1

for j = 1 to icolcount-1

''copies data from the destination file to the source file

objdestSheet.cells(i-3,j)= objsrcsheet.cells(i,j)

next

next

objsrcfile.save

objdestfile.save

''This is again a customised code to delete a sheet from excel file. 

objdestfile.sheets("Previous Day").Delete

'' This step renames the sheet in the destinstion file.

Set objWrksheet = objdestfile.Worksheets("Today")

objWrksheet.Name = "Previous Day"

set objWrkSheet = nothing

''Rename another sheet in the workbook

Set objWrksheet = objdestfile.Worksheets("temp")

objWrkSheet.Name = "Today"

set objWrkSheet = nothing

''Add a temp table at the end of sheets and name it as temp

Set objWrkSheet = objdestfile.Sheets.Add(, objdestfile.Sheets(objdestfile.Sheets.Count))

objWrkSheet.Name = "temp"

''Save the excel file and close the instance of excel aplication

objdestfile.save

objsrcfile.close

objdestfile.close

objexcel.quit

set objexcel = nothing

msgbox "done"

end sub

How to send e-mail using outlook object in QTP

This post explain how to send e-mail using microsoft outlook object with or without any attachments. In the next article we will explain how to read information from an e-mail message.

''Function Description - This function sends a mail to end user
'' Arguments - SendtoUser - Mail will be send to user mentioned in send to
''Subject - Subject of the mail
''Body - Text of the mail
''Attachment - Path of attachment to send e-mail
SendMailUsingOutlook "SendToUser@test.com; testZ@test.com"," Subject", "Body", ""

Function SendMailUsingOutlook(SendToUser, Subject, Body, Attachment) 

'' create an object of outlook application and creating a new e-mail object
    Set objOutlook=CreateObject("Outlook.Application") 
    Set objMail=objOutlook.CreateItem(0) 

'' Provide details of the mail object
    objMail.to=SendToUser
    objMail.Subject=Subject 
    objMail.Body=Body 

'' attach a file with the e-mail, provide path of the attachment in the folder
    If (Attachment <> "") Then 
        objMail.Attachments.Add(Attachment) 
    End If 
'' Send the e-mail
    objMail.Send 
'' close the instance of Outlook application object
    objOutlook.Quit 

''release the object created    Set objMail = Nothing 
    Set objOutlook = Nothing 
End Function 

How to compare 2 dictionary object and report the differences in QTP results

This post discusses how to compare two dictionary objects and display the difference between the values in two dictionary objects in table format in QTP results using Reporter.logevent.

''function to compare two Dictionaries 

Public Function Comfunct_CompareDictionary(ObjDictBase, ObjDictGen)

Comfunct_CompareDictionary = True 
''Check if both dictionary objects have the same number of items
If ObjDictBase.Count <> ObjDictGen.Count Then
Reporter.reportevent micFail,"Data in Dictionary 1 and 2","Data mismatch in the dictionary. there are " & objDictBase.Count & "records in dictionary 1 compared to " & objDictGen.Count & "records in dictionary 2" 
Comfunct_CompareDictionary = False
Exit Function
End If
''Compare keys and values
arrKeys = ObjDictBase.Keys
''Create a table for the differences
strTable = "<table border=""""2""""><tr><td>Key Name</td><td><Value in Dictionary 1</td><td><Value in Dictionary 2</td></tr>"
For i = 0 to uBound(arrKeys)
''Compare key names in both dictionaries and validate keys in one dictionary exists in other dictionary
  If Not ObjDictGen.Exists(arrKeys(i)) Then
''ObjDictBase has a key which ObjDictGen doesn't have, hence exiting the function
Comfunct_CompareDictionary = False
Reporter.ReportEvent micFail, "Key " & arrKeys(i) & " exists in dictionary 1 but not in dictionary 2, " Key difference in both dictionaries"
Exit Function 
  End If
  ''Compare value of each keys in both dictionary and display the difference between 2 dictionary in Qtp results 
  If ObjDictBase(arrKeys(i)) <> ObjDictGen(arrKeys(i)) Then
''ObjDictBase value for arrKeys(i) differs from ObjDictGen, then log the difference in the table created above
Comfunct_CompareDictionary = False
strTable = strTable & "<tr><td>" & arrkeys(i) & "</td><td>" & ObjDictBase(arrKeys(i)) & "</td><td>" & ObjDictGen(arrKeys(i)) & "/td></tr>" 
  End If
Next
If Comfunct_CompareDictionary = True Then
Reporter.ReportEvent micPass, "Comparision of dictionary 1 and dictionary 2","Both dictionary matches"
Else
strTable = strTable & "</Table>"
''Compare value of each keys in both dictionary and display the difference between 2 dictionary in Qtp results 
Call reportinQTPhtmlContent(strTable)
End If 
End Function

''Function to report in QTP result in table format

Public Function reportinQTPhtmlContent(strTable)

Set objdict = CreateObject("Scripting.dictionary")

objdict.Add "Status", micdone

objdict.Add "NodeName", "Table Data"

objdict.Add ("StepHtmlInfo"), strTable

Reporter.LogEvent "User", objdict, Reporter.GetContext

End function

How to add result as HTML table format in QTP test results

We can use reporter.logevent method as explained in code/functions below to add results in  tabular format in QTP test results. Below is an example on how to use reporter.logevent

Public Function func_createHTMLTableFromExcel(strDataFile,strWorksheetName)

Set objXlHandle = Createobject("Excel.Application")

 objXlHandle.visible = false

Set objExcelWB = objXlHandle.Workbooks.Open(strDataFile)

Set objExcelWS = objExcelWB.Worksheets(strWorksheetName)

'Getting column and row count 

 strColumnCount = objExcelWS.UsedRange.Columns.Count

 strTotRows = objExcelWS.UsedRange.Rows.Count

strTable = "<table border=""""2"""">"

'Creating a html table based on content of the excel file

For j=1 to strTotRows

strTable = strTable & "<tr>"

For i=1 to strColumnCount

 strData = Trim(objExcelWS.Cells(j,i))

 strTable= strTable & "<td>"& strData &"</td>"

Next

strTable =strTable & "</tr>"

Next

strTable = strTable & "</table>"

Call reportinQTPhtmlContent(strTable)

'Closing the workbook

 objExcelWB.Close

End Function

Public Function reportinQTPhtmlContent(strTable)

Set objdict = CreateObject("Scripting.dictionary")

objdict.Add "Status", micdone

objdict.Add "NodeName", "Table Data"

objdict.Add ("StepHtmlInfo"), strTable

Reporter.LogEvent "User", objdict, Reporter.GetContext

End function

QTP Script to convert content of excel into a html document

Suppose I have some data in excel file and I want to create a html table from the excel file content. Below code can create a html table based on the content of an excel file.We can understand how to create a html file using scripting.filesystemObject in QTP. 

Example of how to call the function:

func_createHTMLTableFromExcel "E:/test.xlsx","Sheet1","e:/ht.htm"

Code for the function:

Public Function func_createHTMLFromExcel(strDataFile,strWorksheetName,strHTMLFile)

Set objXlHandle = Createobject("Excel.Application")

 objXlHandle.visible = false

Set objExcelWB = objXlHandle.Workbooks.Open(strDataFile)

Set objExcelWS = objExcelWB.Worksheets(strWorksheetName)

'Getting column and row count 

 strColumnCount = objExcelWS.UsedRange.Columns.Count

 strTotRows = objExcelWS.UsedRange.Rows.Count

strTable = "<table border=""""2"""">"

'Creating a html table based on content of the excel file

For j=1 to strTotRows

strTable = strTable & "<tr>"

For i=1 to strColumnCount

 strData = Trim(objExcelWS.Cells(j,i))

 strTable= strTable & "<td>"& strData &"</td>"

Next

strTable =strTable & "</tr>"

Next

strTable = strTable & "</table>"

set objFSO=Createobject("scripting.FileSystemObject")

set objtxt = objFSO.createTextFile(strHTMLFile)

    objtxt.write(strTable)

'Closing the workbook

 objExcelWB.Close

 set objFSO =nothing

End Function

How to add Attachments in QC during test script execution in QTP

Using below code, we can add attachment to QC during test script execution:

''This function will add the required attachment in QC, for Location defined by argument strLocationinQC

Function func_AddAttachmentToQC(FilePath,strLocationinQC)

On Error Resume next

If (ucase(strLocationinQC) = "TEST") Then

Set objCurrentTest = QCUtil.CurrentTest.Attachments

Set objAttach = objCurrentTest.AddItem(Null)

ElseIf(ucase(strLocationinQC) = "TESTSET") Then

Set objCurrentTest = QCUtil.CurrentTestSet.Attachments

Set objAttach = objCurrentTest.AddItem(Null)

ElseIf(ucase(strLocationinQC) = "TESTRUN") Then

Set objCurrentTest = QCUtil.CurrentRun.Attachments

Set objAttach = objCurrentTest.AddItem(Null)

End If

objAttach.FileName = strFilePath

objAttach.Type = 1

objAttach.Post

objAttach.Refresh

End Function

Examples of Working with WebTable in QTP

HTML normally has many webtable and we can extract a lot of information using webtable methods in QTP. In this post we will discuss various methods by which we can used for webtable object.

Example 1: Validate if a webtable exist

boolExist = Browser(…).page(…).webtable(…).exist

Example 2 : To Find number of rows and columns in a web table

intRowCnt=Browser("Google").Page("title:=.*").WebTable(“name:= TTable").RowCount
For r=1 to intRowCnt
‘’ This will loop through each row and tell count of column in each row.
intColCnt=Browser("Google").Page("title:=.*").WebTable(“name:=TTable").ColumnCount(r) 
MsgBox intColCnt
Next

Example 3: How to get data in a particular cell in the data table

strData= Browser("Google").Page("title:=.*").WebTable(“name:=TTable").GetCellData(r,c)
Where r = row number and c = column number

Example 4: Using childObject method to find objects of a particular type within a webtable

Public Function func_findObjectinaWebTable(strObjType)
Set objDesc=Description.Create
objDesc("micclass").value=strobjType
set objChild=Browser("Google").Page("title:=.*").WebTable(“name:=TTa").ChildObjects(objDesc)
func_findObjectinaWebTable = objChild.count
End Function

Example 5: Using HTML DOM to get count of objects in a webtable

Public Function func_findObjectinaWebTable(strTagName)
set objChild= Browser("Google").Page("title:=.*").WebTable(“name:=TestTable").object.getElementsbyTagName(strTagName)
func_findObjectinaWebTable = objChild.length
End Function

Example 6: Using ChildItemCount and childItem in webtable to extract information

Public Function func_findObjectinaWebTable(strObjType,introw,intCol)
ob0jChildCnt=Browser("Google").Page("title:=.*").WebTable(“name:=TestTable,"index:=0").ChildItemCount(introw,intcol,strobjType)
If objChildCnt >0
Childitem will return object oftype defined in arguments for childitem
Set ChldItm = Browser("Google").Page("title:=.*").WebTable(“name:=TestTable,"index:=0").ChildItem(introw,intcol,strobjType,0)
If (strobjType = "Link" or strobjType ="WebEdit") Then
ChldItm.Click
ElseIf(stobjType = "WebCheckBox") Then
ChldItm.Set "ON"
End If
End Function

How to Use GetElementsBytagName Method in QTP explained with Code

In this post, we will discuss how to use GetElementsbyTagName to input values in a edit box, count number of editbox, links , and count number of rows in a particular table. Please suggest in case user miss anything in the code. We can similarly use GetElementsbyTagName in a variety of purposes and manipulate the html

Code to find number of links in a page.

set objLink = Browser("title:=.*","index:=0").Page(title:=.*).Object.GetElementsByTagName("a")

intLinks = objLink.length

Code to Identify number of edit box in the page.

set objLink = Browser("title:=.*","index:=0").Page(title:=.*).Object.GetElementsByTagName("input")

intLinks = objLink.length

Code to input Data in a field using HTML DOM

set objEdit = Browser("title:=.*","index:=0").Page(title:=.*).Object.GetElementsByTagName("input")

for each editbox in objEdit

       If editbox.classname = <strClassName>

editbox.innertext = <strSetVsalue>

Exit For

     End If

Next

Code to count number of rows in a table

set objTable = Browser("title:=.*","index:=0").Page(title:=.*).Object.GetElementsByTagName("table")

for each Tbl in objEdit

         If Tbl.classname = <strTableClassName>

set objrow = tbl.getelementsbyTagName("tr")

Msgbox objrow.length

        End If

Next

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"