In the previous article, we parameterized a test for a Login process with the help of QTP’s Local DataTable. This topic covers the same concept with the help of an (external) Excel file. The process will remain the same, but how we extract data differs. If you are looking to understand Parameterization concepts and need a quick introduction, I would recommend you to read the following article: Introduction to Parameterization with QTP’s Local DataTable
Before we start the process of extracting data from Excel, and using the extracted data in our automated process, we must first understand the Excel Automation object. Before that even, we must understand the CreateObject function. The CreateObject function creates and returns a reference to an Automation object1. In other words, usage of CreateObject in terms of Excel will return the reference of Excel’s Automation object. This reference will enable us to use Excel’s methods for data retrieval. Below is the syntax of CreateObject:
Set Var = CreateObject(servername.typename [, location])
The Set keyword is used here because we have to bind out variable to an object. The Set statement assigns an object reference to a variable or property, or associates a procedure reference with an event2. We can use the syntax above in terms of Excel and create a reference object that will hold methods of the Excel application:
Dim xlApp 'Excel Application Automation Object Reference Set xlApp = CreateObject("Excel.Application")
Next, we will create a reference to the Excel WorkBook, which is the file that we will open to retrieve data from. The workbook we’re working with is located in the root folder: C:\ and its name is TestFile.xls. Thus, we will substitute the pathName\fileName in our code below:
Dim xlBook 'Reference to the workbook located in C:\ 'Set varExcelWorkBook = ExcelObjectReference.WorkBooks.Open("File") Set xlBook = xlApp.WorkBooks.Open("C:\TestFile.xls")
Next, let’s create a reference to the Excel Spreadsheet (Sheet1, Sheet2, Sheet3..) which contains the data we need to drive our parameterized script. Our data is contained in Sheet1 of our workbook, thus, we will use it in our code below:
Dim xlSheet 'Reference to Sheet1 'Set varExcelWorkSheet = varExcelWorkBook.WorkSheets("Sheet") Set xlSheet = xlBook.WorkSheets("Sheet1")
Below is a snapshot of the spreadsheet, with headings in Row 1 and data starting from Row 2:
To retrieve the number of rows and columns used in the table, we will use the code below:
'Total Rows iRows = xlSheet.UsedRange.Rows.Count 'Total Columns iCols = xlSheet.UsedRange.Columns.Count
Lastly, we will retrieve the data from Excel:
'First UserName is stored in the first column (Column A), second Row (A2) of the spreadsheet sUserName = xlSheet.Rows(2).Columns(1).Value 'First Password is stored in the second column (Column B), second Row (B2) of the spreadsheet sPassword = xlSheet.Rows(2).Columns(2).Value
I think we have covered the concepts needed to parameterize our script directly from an Excel file. Now, let’s use the same example we did in the previous article to parameterize the Login process for Mercury/HP’s demo AUT.
1. Launch application 2. Enter UserName/Password from DataTable and click Login 3. Verify if the Find Flights page appears 4. If Find Flights page appears, iteration passed. 5. Return to Home Page and start Step #2
'Step 1: Launch Application SystemUtil.Run "iexplore.exe", "http://newtours.demoaut.com"
'Step 2: Enter UserName/Password from DataTable and click Login 'Retrieve UserName and Password from "iRow" rows and columns A & B sUserName = xlSheet.Rows(iRow).Columns(iUserNameCol).Value sPassword = xlSheet.Rows(iRow).Columns(iPasswordCol).Value 'Parameterization Block: With Browser("title:=Welcome: Mercury Tours", "index:=0") .WebEdit("name:=userName").Set sUserName 'Parameter 1: UserName .WebEdit("name:=password").Set sPassword 'Parameter 2: Password .Image("name:=login").Click End With
'Step 3: Verify if the Find Flights page appears If Browser("title:=Find a Flight: Mercury Tours:", "index:=0").Exist(10) Then 'code End If
'Step 4: If Find Flights page appears, iteration passed. If Browser("title:=Find a Flight: Mercury Tours:", "index:=0").Exist(10) Then Reporter.ReportEvent micPass, "Iteration " & iRow-1, "UserName: " & sUserName & " is valid" Else Reporter.ReportEvent micFail, "Iteration " & iRow-1, "UserName: " & sUserName & " is invalid" End If
'Step 5: Return to Home Page and start Step #2 If Browser("title:=Find a Flight: Mercury Tours:", "index:=0").Exist(10) Then Browser("title:=Find a Flight: Mercury Tours:", "index:=0").Link("text:=Home").Click Reporter.ReportEvent micPass, "Iteration " & iRow-1, "UserName: " sUserName & " is valid" Else Reporter.ReportEvent micFail, "Iteration " & iRow-1, "UserName: " &sUserName& " is invalid" Browser("title:=Sign-on: Mercury Tours", "index:=0").Link("text:=Home").Click End If
Steps 1-5
We can now combine all the 5 steps into a single code block:
'Steps 2-5 Dim xlApp, xlBook, xlSheet Dim iRow, sUserName, sPassword CONST iUserNameCol = 1 'UserName is in Column A CONST iPasswordCol = 2 'Password is in Column B Set xlApp = CreateObject("Excel.Application") Set xlBook = xlApp.WorkBooks.Open("C:\TestFile.xls") Set xlSheet = xlBook.WorkSheets("Sheet1") 'iRow = 2 because data to be driven starts from Row #2 For iRow = 2 to xlSheet.UsedRange.Rows.Count 'Retrieve UserName and Password from "iRow" rows and columns A & B sUserName = xlSheet.Rows(iRow).Columns(iUserNameCol).Value sPassword = xlSheet.Rows(iRow).Columns(iPasswordCol).Value 'Parameterization Block: With Browser("title:=Welcome: Mercury Tours", "index:=0") 'Step 2 .WebEdit("name:=userName").Set sUserName 'Parameter 1: UserName .WebEdit("name:=password").Set sPassword 'Parameter 2: Password .Image("name:=login").Click End With 'Step 3: If Find a Flight page appears, go back to Home If Browser("title:=Find a Flight: Mercury Tours:", "index:=0").Exist(10) Then Browser("title:=Find a Flight: Mercury Tours:", "index:=0").Link("text:=Home").Click 'Step 4: Pass the iteration Reporter.ReportEvent micPass, "Iteration " & iRow-1, "UserName: " &sUserName& " is valid" Else 'Step 5: Fail the iteration and return to the Home page Reporter.ReportEvent micFail, "Iteration " & iRow-1, "UserName: " &sUserName& " is invalid" Browser("title:=Sign-on: Mercury Tours", "index:=0").Link("text:=Home").Click End If Next xlBook.Close xlApp.Quit Set xlSheet = Nothing Set xlBook = Nothing Set xlApp = Nothing
When the above code executes, the follow results will be populated in QTP:
Parameterization with Excel: Results
I hope this article will help automation developers create parameterization with Excel. The concepts covered in this article are quite basic, but they should serve as a baseline to creating parameterization through Excel which can give users more control over how they data-drive their tests. In the coming articles, I will show how Excel can be used as a data-source with performance that QTP’s DataTable just doesn’t provide.
I hope you find this useful :)
References
1. CreateObject Function – MSDN
2. QuickTest Professional Reference Help File – Set Statement
If you have any questions, please ask them in the comments section. If your query is confidential, please use the Contact Form to send me an e-mail instead.
FaceBook
LinkedIn
Twitter
YouTube
{ 1 trackback }
{ 17 comments… read them below or add one }
what if i need to change the URL every time?
Hi Padma,
There are several things to consider if the URL changes:
1. Are the object descriptions changing?
+ If no, then only the URL would need to be parameterized. The new table will now look like this:
+ If yes, the object descriptions would need to be parameterized along with the URL. The new table will now look like this:
Thanks for the post. Very clearly articulated. As you said its quite basic, May I know what are the advanced concepts in this topic.
Have a great time. Keep Smiling.
Hi Indra,
Thank you. :)
I have a few drafts that I need to finalize. They should be out soon :)
HI Anshu,
Great Article!!!
We are looking forward for Advanced Articles.
Thank you. :)
The advanced articles on this topic should be out soon :)
Hi Anshoo,
I got a chance to visit the “.Find” method like: “Obj_WSheet.UsedRange.Find ()” to find the Text from the WorkSheet.
It would be nice, if you could explain this method.
Thanks in Advance!!
Regards,
Devendra Sharma
Hi Devendra,
The .Find method returns the ‘Found Cell’ object. This can be converted into its respective Column/Row values. For example, if you have an Excel sheet with the following cells:
and you would like to find the destination cell for Password, you could use the
findin the following manner:Set xlApp = CreateObject("Excel.Application") Set xlBook = xlApp.Workbooks.Open("C:\Test.xls") Set xlSheet = xlBook.Worksheets("Sheet1") Set oRange = xlSheet.UsedRange Set oCell = oRange.Find("Password") MsgBox oCell.Column MsgBox oCell.Row xlBook.Close xlApp.Quit Set xlSheet = Nothing Set oRange = Nothing Set xlBook = Nothing Set xlApp = NothingThis will give you the corresponding
RowandColumnentities for the string.Thanks Anshoo!!
“.Find” method gives only the First occurance of the Pasword “Test123″, now I want to get all the occorance of the password “Test123″. I feel we can do this with the help of “.FindNext” method. It would be nice, if you could please expain “.FindNext” method.
Set xlApp = CreateObject("Excel.Application") Set xlBook = xlApp.Workbooks.Open("C:\Test.xls") Set xlSheet = xlBook.Worksheets("Sheet1") Set oRange = xlSheet.UsedRange Set oCell = oRange.Find("Test123") MsgBox oCell.Column MsgBox oCell.Row xlBook.Close xlApp.Quit Set xlSheet = Nothing Set oRange = Nothing Set xlBook = Nothing Set xlApp = NothingRegards,
Devendra Sharma
Devendra,
You can use
.FindNextto find the next occurrence of the string.Please find the Test.xls:
Hi Anshoo,
When I am executing the below code, it thows the following error message.
Set xlApp = CreateObject("Excel.Application") xlApp.Visible=True Set xlBook = xlApp.Workbooks.Open("C:\Test.xls") Set xlSheet = xlBook.Worksheets("Sheet1") Set oRange = xlSheet.UsedRange For i=0 to 10 Set oCell = oRange.Find("Test123") oCell.Interior.ColorIndex=25 MsgBox oCell.Column MsgBox oCell.Row Set oCell=oRange.FindNext("Test123") Next xlBook.Close xlApp.Quit Set xlSheet = Nothing Set oRange = Nothing Set xlBook = Nothing Set xlApp = NothingThanks,
Devendra Sharma
When you use
.FindNext, you would need to provide the original reference that you used the first time you used.Find. So, it should like this:Set xlApp = CreateObject("Excel.Application") xlApp.Visible=True Set xlBook = xlApp.Workbooks.Open("C:\Test.xls") Set xlSheet = xlBook.Worksheets("Sheet1") Set oRange = xlSheet.UsedRange Set oCell = oRange.Find("Test123") For i = 0 to 10 oCell.Interior.ColorIndex=25 MsgBox oCell.Column MsgBox oCell.Row 'Notice the oCell here. The object reference has been used instead of the string Set oCell = oRange.FindNext(oCell) Next xlBook.Close xlApp.Quit Set xlSheet = Nothing Set oRange = Nothing Set xlBook = Nothing Set xlApp = NothingHello Anshoo – Thanks a lot!!! for your co-operation.
This code is working fine. But I am facing one problem, if the text be searched is in the following rows like in 1st Row, 25th Row and 90th Row then the below code first find all the text and then iterate the loop up to 90 times. This creates a performance issue.
Please suggest how we can setup our loop so that it can only be iterate as the number of Text to be searched.
For E.g: if in the file “Test123″ is placed only 4 places with in the 90 rows then loop should be iterate only 4 times.
For i = 0 to No_Rows-1
oCell.Interior.ColorIndex=40
‘MsgBox oCell.Column
‘MsgBox oCell.Row
Msgbox “No of Iteration #” &i
‘Notice the oCell here. The object reference has been used instead of the string
Set oCell = oRange.FindNext(oCell)
Next
Regards,
Devendra Sharma
You’re right.. the code will indeed search sequentially. I don’t think there is a way to use this method to search in a way that suits performance, which is why I never use this technique to retrieve data from Excel. Please refer to this article: http://relevantcodes.com/qtp-creating-a-highly-efficient-test-data-dictionary/ which shows a way to retrieve data, build a dictionary object from it at excellent performance. I really like that technique, and 90% of the times I use either that or replace the ADO query with a Range object (as shown in RelevantCodes[1]One Framework).
Thanks a lot Anshoo!!!
:)