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:
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") 'Step 1: Launch Application SystemUtil.Run "iexplore.exe", "http://newtours.demoaut.com" '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 :)
{ 142 comments… read them below or add one }
Hi Anshoo,
I need information of what are the different “Type Libraries” that can be used in QTP . I am also looking for some internal information of those type libraries. where can i find this info in online (any website link)?
How do I copy the entire column but skip the first row?
Assuming all bojects are already created, here is the code to copy column from one sheet and paste it to another sheet in same file.
Set objRange=objBook.Worksheets(“Action1″).Range(“D1″).EntireColumn
objRange.Copy
objBook.Worksheets(“Action2″).Range(“D1″).PasteSpecial Paste =xlValues
It copies the entire column, if only I want to skip first row , how do I do that?
Thank you
Hi Anshoo
I just started my career in Automation Testing.I wanted to know that what are the areas i should stress more.
Prashant
Hi,
I want to update the excel when the excel is opened. Anyone has any idea about that..
Thanks,
Anuj
Anuj, you can bind to Excel if its instance is open (or will open if closed) using GetObject.
Hi Anshoo,
Pls help me out in the below scenario.
Ex- Firstly we order a book or some thing in external portal and in the final stage, Order ID wil be generated. So here how can i extract that Order ID and store in qtp and use the same Order ID again in internal portal..
Thanks in advance.
Mallikarjun, there are several ways you can do this:
1. Store the value in an Environment variable
2. Store the value in a Global variable
3. Store the value externally – text file, xml file, xls file etc.
tHANKS
Hi how to validate the present in the excel sheet.
i mean in sheet i have 3 fields ie’s- name: ,password, url
how to check is it hyperlink or plain text
ls answer any one
Firstly, I enter first name and last name, hit search button, to find data in another web page with address and other details for that user. Secondly, I want to compare address populated in the fields of that web page with the address present in the table existing at back end for nearly 10k users.
Please suggest how to proceed
Hello Ansoo,
I need some assistance, I would like to check the excel sheet whether the required string (“some name”) is present in the excel sheet or not, and also I would like to know how many times it displayed.
and need to validate the dates–the dates must be in B\W particular period like 12 months
Please assist
Regards
Umamahesh
Mahesh: see if you can find some helpful code in the class library here: http://relevantcodes.com/excelutil-class-library-excel-utility-methods/
Hi Anshoo,
I need your help am trying to automate to fill the forms at atime using qtp.
My form contains 6 fields and these fields are like customer,customername,ID,mail address,phone number and these are differnt from one customer to another . All will put in excel in local desktop and QTP recognises and filled the form automatically.
Username mailAddress ID Name Phone Number Account Number Address
smith 5678 Smith 986712352 NA NA
zak
Hi Zak,
Just put the 7 th field as “status” and when you create a form in the 7th filed give out put “completed”
next time when you run use if condition to get start from the fields which have not used
Regards
Umamahesh
Vijay, in your Excel file, you can create data that is customer specified under the following columns: Customer, Customer Name, ID, Mail Address, Phone Number etc. Once you start filling data, first match in Excel the customer you’re trying to fill out. Once you find the customer in the Excel file, use the row index to drive data to your app.
Hi,
I want to calculate number of rows in a particular colummn
Case1 case2 case3 case4
abc abc sbv wer
def hiu wer
ghi uji
jkl
i want to find row count in each column
Senthil: I would assume your question is for a WebTable. You can use the ColumnCount method to do so. Syntax:
hello! Anshoo
How to copy weblist items into excel sheet…
Sushil: Retrieve the items using ‘all items’ property. Use Excel COM to either paste the entire string retrieved from ‘all items’ or split them and paste them in different cells.
hey anshoo,
thanks alot for ur articles on QTP, as a beginner I find them very useful :) looing forward for more articles.
Hi Anshoo,
Hats off to you and your work… :) ..
I am new reader of your post.:)
I WONT only appricitae if i will come across some thing new info i will share by this post…
You are simply genius
Thanks,
Sanjay
Hi Anshoo,
I have been following your QTP postings. You provide a very high quality information. I have a question. I create a Test with 10 actions which does login into a website and goes thru a workflow which involves opening few beowser windows and considerable amount of data. It currently runs fine for one iteration. Now I want to put all my data into an Excel file and use it for multiple iterations. Can you suggest what is the best option? Is it better to write a script which loads the data from Excel file and then loops thru and runs the Test (all actions) for multiple iterations? Your help is greatly appreciated.
Thanks,
Robert.
Robert,
If all your Actions complete 1 workflow, then I would recommend using just the Global DataSheet and adding the relevant data to it. Once this is done, you can set your test to execute all of Global DataTable’s rows. The affect this will have is:
1. Test starts
2. Iteration1
-> Action1 executes using data in GlobalSheet (DataTable.Value(“Column”, dtGlobalSheet)
-> Action2 executes using data in GlobalSheet (DataTable.Value(“Column”, dtGlobalSheet)
-> ActionN executes using data in GlobalSheet (DataTable.Value(“Column”, dtGlobalSheet)
3. Iteration2
-> Action1 executes using data in GlobalSheet (DataTable.Value(“Column”, dtGlobalSheet)
-> Action2 executes using data in GlobalSheet (DataTable.Value(“Column”, dtGlobalSheet)
-> ActionN executes using data in GlobalSheet (DataTable.Value(“Column”, dtGlobalSheet)
Hello,
I sorry perd advanced if my english look’s like bad, i hope you will understand that i want to explain.
Thanks to your article, very easy to understand.
I’m using Qtp 9.5 and i get a issue in a test using excel. This test is a performance test about executing time for a current developpement software and i want to write in an excel sheet the result so.
I import my sheet using ImportSheet method
I get time and write it in a data table, then i export via exportsheet method.
When i try to open my updated file i get a “Data may have been lost” on a pop-up excel.
I search for few hour before find the problem. It’s probably because i write some macro on my excel document, with user form to make my file easy to use.
So i can saw that Qtp delete all of my macro after the test.
My question is, Do you know why qtp erease my macro ? The sheet concerning by the macro wasn’t imported on my Qtp script.
I hope you understand.
Regards
Hello,
I have witnessed this behavior and you are right in saying that when you “Import” the DataTable to QTP, it will get rid of all macros + all the formatting. When QTP exports it back to your system (drive), it will remove all the formatting + all the macros. What you will see after exporting is the same format you do in the design-time table.
I do not know a way that can revert this behavior. This is one of the biggest reasons why I have not stuck with DataTable, but used my own techniques using Excel COM/ADO to make better use of it while keeping all my formatting + macros.
Hi Anshoo,
How can i fetch the Data from excel,Everytime when i am running my script,But i want to capture diffrent Data not a same one
Order State
vgd7 Mi
6yhy NY
7347 GA
I want this data on my script but when i am trying to run the script it’s take 1 row value and then start a New test.But i want a Data comes on my script Automatically diffrent only 1.When i am trying to use my script.Please help
Navdeep: Do you mean to use this through the DataTable? If yes, you can try the
SetNextRowor theSetCurrentRow(Row)methods.Hi Sir
The information which u provided is more useful to me.
Please notify me the followup comments vis e-mail.
write a script to create multiple users in gmail by taking data present in excel file
Hi Anshoo,
can you please explain the difference between DATA DRIVEN and PARAMETERIZATION with an example.
Thanks
Radhika
Radhika, I can only say that parameterization makes it possible to build a data-driven structure :)
Very nicely written , thanks
Awesome work Anshoo…
I would really like to see your framework using the OR instead of DP. I know your said with a little tweaking we can get this accomplished, but it would difficult for us to dive through your code and figure where to enhance it.
I guess I have to wait on the next version…
Regards,
Robert
Robert: I do have plans to creating another framework for the community. I would like to create a Data-Driven framework, but do not know which application I can use. But yes, it will show usage of both OR and DP.
one excel sheet is there like this
1 2 3
4 5 6
7 8 9
i want to get o/p like
1 4 7
2 5 6
3 6 9
… thanks in advance
Ejiaj: See here: http://www.mrexcel.com/forum/showthread.php?t=23012
Hi Anshoo,
I have been using your framework and i had couple of doubts;
- Is it possible to merge the interpritor and the implimentor within one single script? i.e -Is it possible to club Descriptive programing and Object Repository in a single script? If yes what could be the approach.
-Is it possible to switch from Interpritor to implimentor (or Vice Versa) in a script?
Thanks,
Prasad.
@Prasad: Yes, you can merge both classes into one. This framework currently does not support OR, but it can with a few tweaks – you will have to create a bridge that determines when you use an OR style description as opposed to a DP style. Also, the objects must be present in the OR and associated with the test.
I have used the ‘Nothing’ with the Excel application reference variable, still i got the isssue as “The DataTable.ImportSheet operation failed.
Cannot open the C:\Users\Sahasra2\Desktop\NARI\d\Testing\insertec\Test Reports\Testcases.Useraccount.Insertec4.xls file because it is already open in another application”
PLZ HELP ME OUT IN THIS REGARD
Narayana,
You may also have to ‘QUIT’ the application. By setting the instance to ‘Nothing’, you are simply releasing it, not terminating the process.
Hi Anshoo,
I am new bee to this web site and its damn good one ever seen and i am new to automation testing as well.
Whenever i tried to execute my script, the excel sheet get locked and i am getting the error as “the respecitve sheet is already open”. How can over come this issue.
Thanks,
Narayanarao.T
Narayana,
What may be happening is, you are creating an instance of Excel, and not closing it at the end of the script. Or, you are creating two different instances within the same script and trying to overwrite an open Excel file. Can you share your code here? I may be able to help once I see it.
Thanks- Anshoo
Hi…
Have a question that regaurds passing blank values back to the objects using datasheets. For example, I’ve just recorded all the objects on the page (radio,webedit,weblist…etc) and I parameterize them with datatable. How can I just leave the cells in the datasheet blank(or add “SKIP”) that I don’t care to answer or SKIP. For example row 2 below.
Browser(“B”).Page(“1″).WebList(“customerBilling”).Select DATATABLE(“Billing”,sheet)
Browser(“B”).Page(“1″).WebRadioGroup(“useCallerAs”).Select DATATABLE(“Caller”,sheet)
Browser(“B”).Page(“1″).WebEdit(“timer”).Set DATATABLE(“Timer”,sheet)
Datasheet:
Billing | Caller | Timer
Row1 Cash | Rex | 4
Row 2 SKIP | SKIP | SKIP
Hi,
Thanks for the article and your explanation to each and every topic is excellent.
I have a question. I have created 2 actions. The Action 3 is called from Action2. In action 3 , i have parametrized using Excel. The Excel sheet has 4 data and all the 4 values are retrieved for two times. can you please help why this happens. below is the script. Can you tell me why, the data are pulled for 2 times?
Excel Sheet:
——————
AccountNumberN AccountNumberP
!ӣ$%^& 1234567
qazw123 1111111
123qaz 9999999
858 5858 55555
——————————————-
Dim iRow, sAccountNumber
CONST iAccountNumberNCol = 1 ‘AccountNumberN is in Column A
CONST iAccountNumberPCol = 2 ‘AccountNumberP is in Column B
Set xlApp = CreateObject(“Excel.Application”)
Set xlBook = xlApp.WorkBooks.Open(“C:\Program Files\Mercury HP\test.xls”)
Set xlSheet = xlBook.WorkSheets(“Sheet1″)
iRows = xlSheet.UsedRange.Rows.Count
iColumns = xlSheet.UsedRange.Columns.Count
sAccountNumberN = xlSheet.Rows(2).Columns(1).Value
sAccountNumberP = xlSheet.Rows(2).Columns(2).Value
For iRow = 2 to xlSheet.UsedRange.Rows.Count
‘Retrieve Positive and Negative values for AccountNumber from “iRow” rows and columns A & B
sAccountNumberN = xlSheet.Rows(iRow).Columns(iAccountNumberNCol).Value
sAccountNumberP = xlSheet.Rows(iRow).Columns(iAccountNumberPCol).Value
Browser(“Test”).Page(“Test”).WebEdit(“page2.page2Form.balance_4″).Set sAccountNumberN
Browser(“Test”).Page(“Test”).WebEdit(“page2.page2Form.balance_4″).Set sAccountNumberP
Next
xlBook.Close
xlApp.Quit
Set xlSheet = Nothing
Set xlBook = Nothing
Set xlApp = Nothing
Hi,
i m tring to invoke https://abc.com
but not able to set certificate in script..so i need to do something on browser..or smwer to set it on QTP.
pls help me i am neew to this tool,
thanks
Do you mean installing site-certificate on the browser??
Hi Anshoo
suppose one page is like that
when i click on that link it should go to next page.but it’s not identify the link.only it shows that
JavaWindow(“ty”).JavaTable(“hh”).SelectRow “#0″
how it identify that link?
pls reply me
Thanks
hi anshu i have one question. recently i was asked this question. a data table has 20 rows of data. but we have to get data on 4th 7th and 11 th row only. how can we instruct qtp to do so.
Sukumar,
You can use DataTable.SetCurrentRow to focus on the target row. Example:
hiii anshoo,
Can u explain me configuration management tool with example?
Thanks
Hi Anshoo,
Thanks for the excellent information that you share. I am suddenly facing a problem while importing my excel sheet into the datatable.
The datatable has the first row similar to the column headings.
My excel workbook RollOffRequest has the following entries in the sheet “BulkUpload” in the first row:
Name_of_resource No_of_Days
I am using the following commands to import the BulkUpload sheet
strSheetName = Parameter(“SheetName”)
Datatable.AddSheet strSheetName
Datatable.ImportSheet GetCurrentPath() & “\DSM_TestData\RollOffRequest.xls”,strSheetName,strSheetName
Can you tell me what is the cause of this issue?
Hi Anshoo,
Excellent piece of information that is going to help me in running the script with data from excel sheet.
Keep up your good work.
Thanks a lot.
Regards,
Guhan
Hi Anshu,
A small question
In my applicatin this line works fine
browser(bDesc).page(pDesc).Link(vdesc).highlight
but using this i can only highlight the Links.Is thr any posibility that i can pass (link,webelement,webbutton) as a parameter and create a single function to verify all of them.
Something like this
vals = “Link”
browser(bDesc).page(pDesc).vals(vdesc).highlight
Vals= “Webelement”
browser(bDesc).page(pDesc).vals(vdesc).highlight
Do reply.
This looks more active so thaught of posting this here as well
Thanks
Hi Anshoo,
Could you please let me know what is Business Process Testing(BPT)?
Can you please let me know the usage of it? I will be higly thankful to you, if you provide me some realtime examples or documents from where i can learn more.
Thanks
Deepak
Hi Anshoo
Fantastic resource centre, many thanks.
I found this site as my requirement is to data drive my tests from an external spreadsheet. That way if we want people to supply the test data, they need not have to access QTP to do so.
We are using QC for version control and I wonder whether I need to add the Excel document in QC as well, just in case the copy on the shared folder is corrupted/deleted/amended in error etc. I looked into this and wasnt sure how I created a link between the Excel doc and the actual Test. To be honest I am not even sure if this si a good idea or not, so would appreciate your thoughts on this.
Thanks
Steve
Hi Steve,
It would be a good idea to store the Excel files to QC, but if the file sizes are too long, it may take up lots of space and may affect the performance of QC. For the other question of Version Control, i think QC may not be the version control like VSS or SubVersion. In QC it just changes the version number each time you make any change but i don’t think you can go back to previous versions when you really want it once the code is modified. I couldn’t find that option anywhere in QC. I would rather prefer SubVersion.
Steve,
We’re also moving towards version control in my current project and as our framework reaches the next version, so do all our components: libraries, tests, data, recovery scenarios. We’re also using Excel as our data-source and its associated with the same version as the rest of our framework. So, as our framework’s version is incremented, all the resources are moved to the new version..
Hi
Kindly reply for My question below….
For example, Am having 3 rows for (Login Name and password) in Global sheet , LIke
Login Name | Password
1)JoJo mercury
2)abc mercury
3)abcd mercury
The criteria for “Pass” in the “Login name” Should have 4chars length, but here in above data (2)nd row consists of three chars and error will be raised and can’t continue to proceed next step … i need to override this error message and move to 3)rd record continuously without stopping.
Negative testing may not be a good idea for Automation testing.
Considering you’re using the DataTable:
If Not Len(DataTable("LoginName", dtGlobalSheet)) >= 4 Then 'code and logic to move to the next iteration End IfAnshoo,
How to compare R1C1 with R1C3, R1C2 with R2C3, R1C3 with R4C3, R2C1 with R3C3 etc…
Excel1.xls :
C1 C2 C3 C4
R1
R2
R3
R4
Excel2.xls
C3
R1
R2
R3
R4
Thanks in advance
=EXACT(A1, C3)
=EXACT(A2, C2)
This might help
Archishu,
That will require customization of this class, or creating code from the scratch..
Hi Anshoo,
We have application completely built on Excel (macros). It is not a web app / not a windows app. First 15 rows were reserved for design. below that around 30 rows and 20 columns. those cells were filled with data (numbers), which will change when some cells are changed. This looks like a bit complicated for automation with no objects and pure excel apps. Is there a way we can validate huge list of values filled in the cells when one of the cell changes??? This is the first time i have seen a application like this. I really appreciate ur help in this.
Thanks in advance
Adwitha,
You can verify any change within Excel even in a given region, but it will be done within Excel using VBA and not through QTP..
URL=”project url”
Call OpenBrowser(URL)
Browser(“openedbytestingtool:=True”).Page(“title:=EPAY Authentication”).WebEdit(“name:=.*txtUserId”,”index:=0″).Set datatable(“Username”,dtGlobalsheet)
A= Browser(“openedbytestingtool:=True”).Page(“title:= “).WebEdit(“name:=.*txtPassword”,”index:=0″).SetSecure datatable(“Password”,dtGlobalsheet)
B= Browser(“openedbytestingtool:=True”).Page(“title:=”).Image(“name:=ctl00\$BodyHolder1\$btnLogin”).Click
If I am assigning variable like this I am getting Syntax error.Please correct me with valid syntax
so that I can do validation for User name and Password
Thanks
Kumar,
This is correct:
This is incorrect:
The above statement states that the SetSecure method is returning a value, but it is not. The variable ‘A’ will always be empty. Same for the statement below:
Hi Anshoo,
As you rightly pointed out, I want to see qtp result in .xls format
Also pls let me know how to get it in .txt format.
Thanks.
I don’t think we can achieve it with .XLS, even with QTP 10.0.
Also, for .txt, the report can be quite hard to read. QTP can create reports in HTML – I would recommend that, or .DOC for that matter.
Hi Anshoo,
I am not able to write a script to decimal values from excel sheet,Could u please help me
Rajendraprasad,
Not sure if I understand the question? Write a script to decimal values?
HI Anshoo,
As you rightly pointed out
can u please provide script for the decimal values like (20.00 , 20.50,20.75etc).Please let me know.
Thanks in advance
Pradeep
Hi Anshoo,
I want to retrive QTP result in an excel sheet.how to retrive ?
can u help me?
Mini,
Can you please elaborate upon your requirement? Do you mean, you want to generate a report in .xls format?
Thanks Anshoo
Your tips really helped me…
Anshoo u r so great
Anshoo,
I am using 5 also its not working.Can u sugest me any other method.
Another thing is i want to retrive a row from excel sheet.suppose i am putting values in 4th row & 5th column
like username:arpi
pwd:mercury
port:1209
pls reply me ..
Thanks
Arpita,
To store values in Excel, the value to be written should be on the right side of the
=sign:Set xlApp = CreateObject("Excel.Application") Set xlBook = xlApp.Workbooks.Open("C:\Test.xls") Set xlSheet = xlBook.Worksheets("Sheet1") xlSheet.Rows(2).Columns(1).Value = "Row2Column1" xlBook.Save xlApp.Quit Set xlSheet = Nothing Set xlBook = Nothing Set xlApp = NothingWhen the value is to be retrieved, the variable that stores the value should be on the left side of the
=sign:Set xlApp = CreateObject("Excel.Application") Set xlBook = xlApp.Workbooks.Open("C:\Test.xls") Set xlSheet = xlBook.Worksheets("Sheet1") sValue = xlSheet.Rows(2).Columns(1).Value MsgBox sValue xlBook.Save xlApp.Quit Set xlSheet = Nothing Set xlBook = Nothing Set xlApp = NothingPS. Please make sure to update the Workbook path and the Sheet name before using the above 2 snippets.
Hi Anshoo
i tried your code.i put 8 in row & E in column.But it shows error unknown runtime error.
can u tell me pls?
can u suggest me any answer for 2nd one?
Thanks
arpita
Try using 5 for the column instead of E. Does that work?
Hi anshoo,
I want to retrive a row from excel sheet like
username :arpita
pwd :mercury
servername:@yahii.com
port:1243
how to retrve can u suggest me some script using qtp
hi anshoo,
i want to retrive a particular cell from excel sheet using qtp scrpt?
how to retive?
Arpita,
With QTP, you can retrieve the entire sheet and import it to the DataTable. If you just want to retrieve a single cell, then you can use the following:
Set xlApp = CreateObject("Excel.Application") Set xlBook = xlApp.WorkBooks.Open("C:\Test.xls") 'remember to update the correct path Set xlSheet = xlBook.WorkSheets("Sheet1") theValue = xlSheet.Rows(1).Columns(1).Value xlApp.Quit Set xlSheet = Nothing Set xlBook = Nothing Set xlApp = NothingHi Anshoo,
I went through all your posts and comments. All these are quite elaborate. I may ask you some irritating questions, please answer me those with example.
What is Action parameters, datatable parameters, How it is useful in Parameterization. Could you please provide your answer with examples.
Thanks
Deepak
Browser(“title:=Welcome: Mercury Tours”, “index:=0″) .WebEdit(“name:=userName”).Set sUserName
sUserName = for this variable we are getting data from excelsheet from step 1-5
1) if 1 or 2 set control means no problem
1) If there are many set control in a program, then what we have to do
Jakka,
I’m not sure if I understood your question. Can you please elaborate?
Really Great website
Thank you!
Hi Anshoo,
I need a solution to for using wild card for parametrization in datasheet.
I have a dropdown which has some values which have been parametrised to be picked up from the data sheet.
Everything is fine upto this point. The problem now arises when the data in the dropdown is dynamic and changes with each run.
e.g. if i am trying to use just “one” vlaue in the drop down everytime I run it, it looks like this
For 1st run: Abc def 463.98
For 2nd run: Abc def 465.34
First few characters(in this case first 8 characters) will “always” remain constant for all the runs. And just to reiterate I can safely ignore the variable part (last 6 characters) in all my runs.
Question is – How can I use a wild card kind of thing in the datasheet so that it will work with each run.
Any help from your side is greatly appreciated.
-Thanks
Al
Hi Al,
You can setup your DataTable to have the following values:
and see how to use ReGex with the Select method in this article.
Hi Anshoo,
Thanks for your reply. Sometimes after deleting the empty rows also it is trying to process on the empty rows. We don’t know the exact reason why it is happening. Can we do something with QTP/VB scripting to handle this. The problem occuring during the regression, some of the modules is stucking up on empty rows and test is not going to next module due to this. I hope this can be handled through script.
With best regards
Rohan
Can you please share the function/code that you are using to write to Excel?
Hi Anshoo, we have a strange problem thats occuring for only some of the Excel files when used in QTP as test data. QTP test is trying to process the empty row as well. For example there are 10 rows of test data are there in excel and while running the test after finishing the 10 rows, QTP is trying to run on the 11th row (empty row) as well so the test is not finishing. Previously whenever this happened we have selected the empty rows from bottom of the excel sheet and deleted the empty rows. it used to work fine but the problem started again. Is there any solution to handle empty rows scenario like this.
Rohan,
Open the Excel sheet, select the last few empty rows and delete them. Even if you have accessed an Excel cell, which may have been left empty will be counted as a used cell. Either that, or you will have to check each data retrieved is null or not. I think deleting all the empty rows towards the end is going to be a better approach though.
How to check one row of the data table toa entair excel sheet.
For example i have the value A=10 in my global sheet ,now i want to check the value 10 in a excel sheet .(it may not in the same row ).
To check for a value in an Excel Sheet, you can use the following:
Function CheckValue(sValue, sFileName, vSheet) Dim xl, book, sheet, range, found CheckValue = False Set xl = CreateObject("Excel.Application") Set book = xl.Workbooks.Open(sFileName) Set sheet = book.Worksheets(vSheet) Set range = sheet.UsedRange Set found = range.Find(sValue) On Error Resume Next If Not found Is Nothing Then CheckValue = True On Error Goto 0 book.Close xl.Quit Set sheet = Nothing Set book = Nothing Set xl = Nothing End FunctionUsage:
MsgBox CheckValue("10", "C:\Test.xls", "Sheet1")Anshoo, is there way to write a function / procedure for a role based login. What i am wishing is as follows.
role | Username | Password
Manager | ABC | XYZ
Accountant | BCD | XXX
..
..
Many roles will be listed
Actual QTP script,
so when I call login(Manager), it should get the values of Manager (ABC and XYZ ) and put that for the same user name and password.
browser().Page().Webedit().Set Username (from that function)
browser().Page().webedit().set password ( from that function)
click.login
I would appreciate your earliest response.
Chopra,
You can try something like this:
Function Login(sRole) Dim Roles, Keys, ix Set Roles = CreateObject("Scripting.Dictionary") Roles.Add "Manager", "Username|Password" Roles.Add "Accountant", "Username|Password" Keys = Roles.Keys For ix = 0 to Roles.Count - 1 If LCase(sRole) = LCase(Keys(ix)) Then sUserName = Split(Roles.Item(Keys(ix)), "|")(0) sPassword = Split(Roles.Item(Keys(ix)), "|")(1) End If Next If sUserName <> "" And sPassword <> "" Then Browser().Page().WebEdit().Set sUserName Browser().Page().WebEdit().Set sPassword End If Set Roles = Nothing End FunctionUsage:
Login "Manager"
Login "Accountant"
I would like to place all the test data sheets in one single excel workbook, currently they are in 10 different excel files (I want do this manually) but i wanted the tests to be run with different sheet names in one single excel workbook.
Thanks Anshoo
Rohan,
If you’re trying to change the name of the sheet, you can follow this code:
Set xl = CreateObject("Excel.Application") Set book = xl.Workbooks.Open("C:\Test1.xls") book.Worksheets("Sheet1").Name = "Registration" book.Save book.Close xl.Quit Set book = Nothing Set xl = NothingIf you would like to change multiple sheet names at once, you can create a
Scripting.Dictionaryand add the present sheet names and the names that you would like to change like this:Set sheets = CreateObject("Scripting.Dictionary") sheets.Add "Sheet1", "Registration" sheets.Add "Sheet2", "Management" Set xl = CreateObject("Excel.Application") Set book = xl.Workbooks.Open("C:\Test1.xls") keys = sheets.keys items = sheets.items For ix = 0 to sheets.Count - 1 book.Worksheets(keys(ix)).Name = items(ix) Next book.Save book.Close xl.Quit Set book = Nothing Set xl = NothingHi Anshoo,
I had a query regarding Excel, Currently i created a some modules as individual QTP scripts and every module has its own excel file as test data in “Sheet1″ of corresponding excel. I would like to merge all the test data excel files into one master excel file with different sheet names and use them in my tests like sheet1 would be renamed “Registration” Sheet2 will be renamed to “Management” etc…..Could anybody have any idea how this can be implemented in QTP.
Thanks in advance
Rohan
Rohan,
Would you like to move all sheets to a single workbook through QTP? Or, would you like to add a new sheet each time your test is run with a different name?
Hi Anshoo,
Thanks anshoo for your reply.
I think i found the problem for this. After i run with 100 rows of data, at certain point above 80 rows it is hanging on the script. It looks like a bug in the application not QTP because, From the point where it stopped i did a navigate and learn and it is capturing the same objects for 83 rows. Fo example there is a Help button on side bar, but it captured it for more than 80 times as object. And when i clicked the button “Highlight in Application” in object repository manager, to test some of the objects it is trying to capture the objects which were not visible on the screen, but it is trying to highlight.
Thanks
Deepak
Deepak: Glad you figured this out. Yeah, I didn’t think it would be a QTP issue because I’ve run tests with tons of iterations and they’ve run without any issues. Well, QTP did crash once or twice, but other than that, its been quite smooth.
Hi Anshoo,
I have tried it with different sets of data. It exactly stopped there. It is not a data related issue.
Thanks for yoiur promt reply
Deepak,
Not sure what the issue can be. It can be multiple things. If you have eliminated all user error possibilities, and feel its a QTP issue, then you may need to contact HP support to get this resolved.
Hi Anshoo,
I have got a strange question for you. We have been working on a Java based applet application, We are using QTP 10.0 with Java addin and all the java patches. we are using excel to populate the data, we have tested this application for hundreds of times with some data, but when we suddenly increased the number of rows of data, it is exactly stopping at row 83, We have tested with different sets of data. It is stopping exactly at row 83. My question is while the excel spreadsheet is loading the data in data table and run the test, Is there any limits that were there on the data table to handle the data, Is it a memory related issue with data table. Is there any solution to fix these kinds of strange behaviours of Excel sheet / QTP Data table. (There is absolutely nothing wrong in the data we entered for all the columns, it is working fine for upto row 82, when it comes to row 83 it is stopping on some object)
Have you guys experienced like this scenario before
Thanks,
Deepak
I just ran a test with 99 rows of data and no such issue.. Not sure what might be causing this. Is it a data issue? Have you tried switching rows around? Does it stop at the exact same point each time?
Hi Anshoo,
We couldn’t find much information anywhere about the use of XML file as Test Data for large sets of data.
DO you have any plans of including this in your website. Thanks in advance.
Deepak
With large sets of data, XML files can be quick painful to maintain and understand which is why I usually prefer a tabular format :)
I will try to write an article on working with XML files though, which should cover this as well.
Thanks a lot Anshoo. You are too fast in the replies. We liked you somuch
Hi Anshoo,
Thanks for your email, Great job. I got query regarding one of the scenario, I want to place one variable (which will be generated runtime), Currently i am capturing that variable and using the MsgBox to display that variable. but i wnated that variable to be moved into excel file TestExcel.xls into Sheet1 and Row 2, Column = 2 .(This would be used to run next test)
Thanks in advance guys
Deepak
Deepak,
try this:
Set oExcel = CreateObject("Excel.Application") Set oWorkbook = oExcel.Workbooks.Open("C:\TestExcel.xls") Set oWorksheet = oWorkBook.Worksheets("Sheet1") oWorksheet.Rows(2).Columns(2).Value = Variable oWorkbook.Save oExcel.Quit Set oWorksheet = Nothing Set oWorkbook = Nothing Set oExcel = NothingThanks a lot Anshoo!!!
:)
Hello 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).
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 = NothingPlease find the Test.xls:
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.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.HI Anshu,
Great Article!!!
We are looking forward for Advanced Articles.
Thank you. :)
The advanced articles on this topic should be out soon :)
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 :)
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:
Dear Anshoo. this worked perfect. this was awesome.
I will tried playing your code by getting the values from excel with 3 columns (Roles, Username, Password) but was getting no results.
I am still trying, but if I can use your expertise I would really appreciate.
Regards
Mohit Chopra
Thanks heaps Anshoo
Mohit,
What is the code that you tried? Excel COM object will be required to retrieve data, and you may need to run it in a loop in order to parametrize it.
Hi All,
How to convert this excel formula into VBScript
=EXACT(A1, C3)
=EXACT(A2, C2)
Thanks
‘Try This. Please Leave a comment, I’ve not tested this
Dim Exl_Obj
Set Exl_Obj = CreateObject(“Excel.Application”)
Exl_Obj.visible = True
Set WB_Obj_1 = Exl_Obj.Workbooks.Open(“C:\Excel1.xls”)
Set WB_Obj_2 = Exl_Obj.Workbooks.Open(“C:\Excel2.xls”)
Set WS_Obj_1 = WB_Obj_1.Worksheets(1)
Set WS_Obj_2 = WB_Obj_2.Worksheets(1)
If WB_obj_1.Cells(1, 1).Value WB_obj_2.Cells(1, 3).Value OR WB_obj_1.Cells(1, 2).Value WB_obj_2.Cells(2, 3).Value
cell.Interior.ColorIndex = 3
Else
cell.Interior.ColorIndex = 0
End if
EXl_Obj.workbooks(“Excel1.xls”).save
EXl_Obj.workbooks(“Excel1.xls”).Close
EXl_Obj.workbooks(“Excel2.xls”).save
EXl_Obj.workbooks(“Excel2.xls”).Close
Exl_Obj.Application.Quit
Set Exl_Obj = nothing
Thanks Vijju I modified the above code the below code is working but it needs some tweaking because it colors all the cells rather than cells with difference, i think “cell.Interior.ColorIndex = 5″ needs to be changed. Anshu Would you tweak this???? Thanks in advance
Dim Exl_Obj
Set Exl_Obj = CreateObject(“Excel.Application”)
Exl_Obj.Visible = True
Set WB_Obj_1 = Exl_Obj.Workbooks.Open(“D:\1.xls”)
Set WB_Obj_2 = Exl_Obj.Workbooks.Open(“D:\2.xls”)
Set WS_Obj_1 = WB_Obj_1.Worksheets(1)
Set WS_Obj_2 = WB_Obj_2.Worksheets(1)
For Each cell In WS_Obj_1.UsedRange
If WS_Obj_1.Cells(1, 1).Value WS_Obj_2.Cells(2, 2).Value OR WS_Obj_1.Cells(1, 2).Value WS_Obj_2.Cells(1, 2).Value Then
cell.Interior.ColorIndex = 5
Else
cell.Interior.ColorIndex = 0
End if
Next
Exl_Obj.workbooks(“1.xls”).save
Exl_Obj.workbooks(“1.xls”).Close
Exl_Obj.workbooks(“2.xls”).save
Exl_Obj.workbooks(“2.xls”).Close
Exl_Obj.Application.Quit
Set Exl_Obj = nothing
Vijju,
You’re correct. We went back and forth with HP with the way version control has been implemented in QC. The increments are in Integer format – 1, 2, 3, 4.. We also wanted major and minor versions but that’s not possible (yet).
its working.thanks for your reply.
Yes….how can i installcerts of my https secured application ? i.e public keys of the other application in qtp?
hi anshoo arora u r providing very gud information on QTP
Thank you Vamsikrishna!
Hi Anshoo,
Need a help from you
am trying to automating the request forms data and the data fields like in below
Username :
mailAddress:
ID :
Name :
Phone Number:
Account Number :
Address :
All the above fields will store in exce and from excel qtp has to get those and fill the sheet automatically
And in excel the format is like in below
Username ID Name Phone Number Account Number Address
smith 5678 Smith 986712352 NA NA
Zak 2314 ZakS 0294678912 NA NA
{ 1 trackback }