Sunday, March 27, 2011

Excel files and QTP

Excel files are the integrated part of the software Testing Process specially automation testing.  QTP uses excel files as default data files. It’s very easy to maintain test data into Excel files. Number of files can vary from one to many in any testing Project. There are several ways to access the excel files into the QTP:

First-QTP Default files: By default each QTP script has a default excel file. For a script with single action (Action1) we will have two sheets. One is global which can be used to access data across multiple actions; another is per Action Sheet with the sheet name as action Name (Action 1).

Example: As shown in the below snapshot we have a new script with only one Action named as “Action1” 


  As shown below this script has only two excel sheets. One is Global and another is “Action1”


As the no of actions will increase into the script number of sheets will also increase correspondingly. All these sheets and there data can be accessed by QTP default methods.
For example: if we have the data as shown below in Global sheet then any of the mehtods written in below script can be used to access the data. There output is also shown in the print screen window

Similarly we can access the data from the local action sheets too. Method written in line no 6 can be used to access data from any of the sheets. As shown in line no 8 we have fetched the data from Global sheet. Thus it enables us to fetch data from any sheet which is available at run time in the script.


Utility Objects:  By using utility objects we can import excel files at the run time , we can add data into the sheets and then export them before exiting the script. It enables us to save the data which is added at run time into the sheet. Following are the excel utility objects these will be explained in other post.


1. DataTable Object
2. DTParameter Object
3. DTSheet Object
Excel Objects (Excel. Application): Another method to access the excel files is by using COM objects. Following are the sample codes to access xls files by using VBscript. These codes can be run as it is in QTP.

Example 1: Following is the example of opening and writing data into xls file.

xls file before writing the data:



Code:

' Open and Write data into a xls sheet

'Declare Variables
Dim objXL,objWrkBk,objWrkSht
'Create excel object
Set objXL=CreateObject("Excel.Application")
'open workbook
Set objWrkBk=objXL.Workbooks.Open("C:\Users\sandeep\Desktop\Training\Excel\ExcelClass-1 - Exam.xlsx")
'Open excel sheet
Set objWrkSht=objXL.ActiveWorkbook.Worksheets("TextOr")
objWrkSht.cells(1,1)=" Hello World"
'Release objects
objWrkBk.Save
objXL.Quit
Set objWrkSht=Nothing
Set objWrkBk=Nothing
Set objXL=Nothing

'xls file after executing the above script:










Example 2: How to copy data from one xls file to another.

This example shows how to create a duplicate copy of the excel file. This code also demonstrates the following:

1. How to get the all sheet names from a collection

2. How to get row count & column count

3. How to create a new workbook and worksheet at run time & How to save them.


' How to copy data from one workbook to another

'Declare Variables
Dim objXL,objWrkBk,objWrkSht
'Create excel object
Set objXL=CreateObject("Excel.Application")
'open workbook
Set objWrkBk=objXL.Workbooks.Open("C:\Users\sandeep\Desktop\Training\Excel\ExcelClass-1 - Exam.xlsx")


'Create new workbook where all the data will be copied
Dim objNewWorkBook,objNewWorkSheet
Set objNewWorkBook=objXL.Workbooks.Add()


' Get all the sheet names one by one and copy them into the 2nd workbook
Dim shts, varSheets
Set shts=objWrkBk.Sheets ' get the sheets collection
For each varSheets in shts
    Set objWrkSht=objWrkBk.Worksheets(varSheets.name) 'open the first sheet
Dim rCount,cCount ' get the row and column count of the worksheet
rCount=objWrkSht.usedrange.rows.count
cCount=objWrkSht.usedrange.columns.count

Set objNewWorkSheet=objNewWorkBook.Worksheets.Add () ' create the 2nd sheet
objNewWorkSheet.name=varSheets.name ' Give the same name as of first sheet

'Copy all Data cell by cell
Dim row,col
For row=1 to rCount
For col=1 to cCount
objNewWorkSheet.cells(row,col)=objWrkSht.cells(row,col)
Next
Next

Next


'Save as the 2nd workbook
Dim wrkBkName:wrkBkName=Year(Now)&Month(Now)&Day(Now)& Hour(Now)& Minute(Now) & Second(Now)
objNewWorkBook.saveas "C:\Users\sandeep\Desktop\Training\Excel\" & wrkBkName & ".xlsx"

''Release objects
objNewWorkBook.save
objNewWorkBook.close
objWrkBk.Close
objXL.Quit
Set objNewWorkBook=Nothing
Set objWrkBk=Nothing
Set objXL=Nothing


Note:

1. Cell be cell code will take quite a long time to create copies of the excel file. To do it faster copy all code in a vbs file and double click it.
2. To change the order of the sheets use the optional parameters in “worksheets.add() “


Example 3: How to find text in the workbook:

There are two methods find the same. Both are demonstrated below

Input:


Code:

'' How to find  text in the workbook

'Declare Variables
Dim objXL,objWrkBk,objWrkSht
''Create excel object
Set objXL=CreateObject("Excel.Application")
''open workbook
Set objWrkBk=objXL.Workbooks.Open("C:\Users\sandeep\Desktop\Training\Excel\ExcelClass-1 - Exam.xlsx")

''Open excel sheet
Set objWrkSht=objXL.ActiveWorkbook.Worksheets("XYZ")

Dim Text2Found:Text2Found="eye liner"
' First & recommended method
Dim objFind:Set objFind=objWrkSht.usedrange.find(Text2Found)
If  objFind is nothing Then
print "text not found"
else
print " Row no from First Method: " & objFind.Row ' find out the row no
print " Column no from First Method: " & objFind.column ' find out the col no
End If

' 2nd method
Dim rCount,cCount
rCount=objWrkSht.usedrange.rows.count
cCount=objWrkSht.usedrange.columns.count
Dim row,col
Dim flgTextFound:flgTextFound=False
For row=1 to rCount
For col=1 to cCount
If lcase(objWrkSht.cells(row,col))=Text2Found Then
print " Row no from 2nd Method: "  & row
print  " Column no from 2nd Method: "  & col
flgTextFound=True
Exit For
End If
Next
If flgTextFound Then
Exit For
End If
Next

'Release objects
objXL.Quit
Set objWrkSht=Nothing
Set objWrkBk=Nothing
Set objXL=Nothing

Output:
















Excel by using ADO: Another method to access excel files is by using ADO objects
Following example demonstrates how to use ADO objects to fetch data from the xls file.

Input File:






Code:



Dim ado
Set ado=CreateObject("ADODB.connection")
ado.open "DSN=Excel Files;DBQ=C:\Users\sandeep\Desktop\Training\Excel\ExcelClass-1 - Exam.xlsx;DriverId=1046;MaxBufferSize=2048;PageTimeout=5;"
Set rs=CreateObject("ADODB.Recordset")
rs.Open "Select Sample  from [Sorting$]",ado


Do until rs.EOF 
For each rec in rs.Fields
If isnull(rec.value) then 
Exit do 
else
print rec.value
end if 
Next
rs.MoveNext
Loop


rs.Close
ado.Close
Set rs=nothing
Set ado=nothing







Output:




17 comments:

  1. Excellent really,

    Babu.V.S.

    ReplyDelete
  2. Your 1st method is really superrrrrrr . Thank You So much

    -bin

    ReplyDelete
  3. Good day! This is kind of off topic but I need some guidance from
    an established blog. Is it very hard to set up your own blog?
    I'm not very techincal but I can figure things out pretty fast. I'm thinking about making my own
    but I'm not sure where to begin. Do you have any tips or suggestions? Cheers

    My webpage: Air Max

    ReplyDelete
  4. Hello, just wanted to mention, I enjoyed this article.
    It was funny. Keep on posting!

    my blog post; Air Max

    ReplyDelete
  5. You ought to be a part of a contest for one of the most useful websites
    on the net. I am going to recommend this blog!

    Here is my web-site: Abercrombie and Fitch

    ReplyDelete
  6. My family members always say that I am wasting my time here
    at net, however I know I am getting familiarity every day by
    reading such nice articles.

    Here is my web site - Abercrombie Fitch Belgique

    ReplyDelete
  7. Oh my goodness! Amazing article dude! Many thanks, However I am encountering troubles with your RSS.

    I don't understand why I cannot join it. Is there anyone else having identical RSS issues? Anybody who knows the solution can you kindly respond? Thanx!!

    Feel free to surf to my blog post converse basse

    ReplyDelete
  8. There's definately a great deal to learn about this issue. I like all of the points you've made.


    Visit my web page - wiki.mangotele.com

    ReplyDelete
  9. Great delivery. Sound arguments. Keep up the amazing spirit.


    My web-site; Abercrombie and Fitch

    ReplyDelete
  10. Great delivery. Sound arguments. Keep up the amazing spirit.


    My web site: http://slc-wireless.com/chaussures-de-football-pas-cher.html

    ReplyDelete
  11. Excellent items from you, man. I have be mindful your stuff prior to and you are just too excellent.
    I really like what you have bought right here,
    really like what you are saying and the way in
    which in which you say it. You're making it enjoyable and you still take care of to stay it sensible. I cant wait to read much more from you. This is really a tremendous site.

    Also visit my webpage Louis Vuitton Bags

    ReplyDelete
  12. Hi! I know this is somewhat off topic but I was wondering which
    blog platform are you using for this site?
    I'm getting sick and tired of Wordpress because I've had problems with hackers and I'm looking at options for another platform. I would be awesome if you could point me in the direction of a good platform.

    my weblog :: Louis Vuitton Outlet

    ReplyDelete
  13. Simply desire to say your article is as amazing. The clarity for your submit is just excellent and that i could suppose you're an expert on this subject. Well together with your permission allow me to take hold of your feed to keep updated with approaching post. Thank you 1,000,000 and please keep up the rewarding work.

    Feel free to surf to my page :: Louis Vuitton Pas Cher (http://nysacpr.org)

    ReplyDelete
  14. A fascinating discussion is definitely worth comment.
    There's no doubt that that you ought to write more on this subject matter, it might not be a taboo subject but usually people do not speak about such topics. To the next! Kind regards!!

    my page visit this link

    ReplyDelete