How do I make my UFT script skip a cell in excel based on the labels on the application screen

Question

I have a very dynamic application to automate using UFT. The labels on the application screen change based on the input provided by the user. I have an excel where I have set the field value for all possible fields present in the application. The challenge is I want my UFT script to pick only those field values for which it finds the field name on screen. Each time I run the script, based on the selection whatever fields apppear on the screen, I want it to pick only those corresponding values from the excel sheet and ignore the other values in that excel sheet.

Eg

Excel has the following vales

Name: Nancy Grade: 8 Hobby: Writing Friend: Veronica BFF: Karen

But in the application on screen only the following labels appear

Name: Hobby: BFF:

I want the UFT script to compare the field label on screen with that in excel and pick those values from excel (in this case Name, Hobby, BFF) and ignore the other fields (Grade and Friend) .

Is this possible?

Are you using the excel file as a QTP native DataTable? and are the fields with dynamic labels in some sort of table, or are they various input boxes with captions? — Trimble Epic
Yes, it is possible. And if you add some sample code, and re-formulate your question to be a little more specific, or even better, add an SSCCE sscce.org, you'll get feedback that is more targeted ;-) — TheBlastOne
That's right. Am using excel file as dataTable. All the fields are input boxes with labels that can be uniquely identified. Until now I have been linking the field names to columns in excel to fill the value. However what my code isn't doing is ignoring columns for which no field label appears on screen. Have used the Visible = True and Visible = False etc but it is still trying to find the field and failing the script. Guess am doing something wrong somewhere. — TryingtoAutomate

Trimble Epic Trimble Epic · Accepted Answer · 2016-05-03T12:00:56

Let's assume your application presents various editboxes on a form for input, and you are using the DataTable object to store your Excel file.

Ok, here's what I would do.

First... I'd gather the objects into the Object Repository. I would create/record each possible edit box as a separate object (within an object for the app itself), making sure that each object is uniquely identifyable by name... (if the edit box controls themselves aren't uniquely named behind the scenes, QTP offers the ability to link to the captions next to them...). Also, if the application merely HIDES the edit boxes, then make sure Visible=True is one of the idenfitying attributes, such that when Visible=False, they are not found.

Once every object is in the OR, then it's a simple matter of checking if they exist.

'assuming your excel file is already imported as the global DataTable
if Window("My App").Editbox("Name").exist(0) then Window("My App").Editbox("Name").Set DataTable("Name")
if Window("My App").Editbox("Grade").exist(0) then Window("My App").Editbox("Grade").Set DataTable("Grade")
if Window("My App").Editbox("Hobby").exist(0) then Window("My App").Editbox("Hobby").Set DataTable("Hobby")
if Window("My App").Editbox("Writing Friend").exist(0) then Window("My App").Editbox("Writing Friend").Set DataTable("Writing Friend")
if Window("My App").Editbox("BFF").exist(0) then Window("My App").Editbox("BFF").Set DataTable("BFF")

Of course, this example is inefficient and can be easily rolled up (i.e. you could make a UDF, and call it from a loop reading field names from an array...) But, it should demonstrate this simple approach.

How do I make my UFT script skip a cell in excel based on the labels on the application screen

3 Answers