Export QTP output data to Excel (2627 Views)
Reply
Super Advisor
Steve Langille
Posts: 399
Registered: ‎04-29-2008
Message 1 of 9 (2,627 Views)

Export QTP output data to Excel

At the end of my QTP script run I want to capture some data from the application page and export that to excel.

I would like the code on how to do that.

Then I want to compare that output data to what I know should be there and place in QTP a pass or fail.

So my question is really two parts:

1. Export data to Excel when a script finishes.
2. Verify that this data is correct.

Thanks!
Steve
Using QTP 9.2 on Windows XP
Please use plain text.
Honored Contributor
Rhonda Robinson
Posts: 3,159
Registered: ‎11-13-2007
Message 2 of 9 (2,627 Views)

Re: Export QTP output data to Excel

There are Excel commands available within QTP. Create the Excel object, then you can perform an Excel.Export function.

Where do you have your expected results? Are they also in an Excel file? If so, you could attach that Excel file as your DataSheet. Then you would simply be able to compare specific values from your application to the values contained within the datasheet.
Please use plain text.
Super Advisor
Steve Langille
Posts: 399
Registered: ‎04-29-2008
Message 3 of 9 (2,627 Views)

Re: Export QTP output data to Excel

Thanks Rhonda!

This is how far I have gotten:

********************************

'Date Ordered
Dim DateOrdered
DataTable("DateOrdered",dtGlobalSheet) = OracleFormWindow("Sales Order").OracleTabbedRegion("Order Information").OracleTextField("Date Ordered").GetROProperty("value")
Msgbox DateOrdered

*********************************

But when I run that I get this error message:

"The set DataTable.Value operation failed. The column does not exist."

Now I am assuming that I first grab the data using RO then I store that in my temp Global datatable. Then I export that
to the external excel document, correct?

So I need some help on how to store it temporarily in my Global datatable.


By the way when I use this command:

Dim DateOrdered
DateOrdered = OracleFormWindow("Sales Order").OracleTabbedRegion("Order Information").OracleTextField("Date Ordered").GetROProperty("value")
Msgbox DateOrdered

The messagebox comes back with the correct date and time from that object.

Steve
Please use plain text.
Honored Contributor
Rhonda Robinson
Posts: 3,159
Registered: ‎11-13-2007
Message 4 of 9 (2,627 Views)

Re: Export QTP output data to Excel

You have to define the field within your datasheet in order to write to it. This is not done within the script, but prior to executing the script.

Simply click the column header of the datasheet shown at the bottom of QTP and give it the same column name you used within your code.
Please use plain text.
Super Advisor
Steve Langille
Posts: 399
Registered: ‎04-29-2008
Message 5 of 9 (2,627 Views)

Re: Export QTP output data to Excel

Ok I seem to be past the hurdle listed above. Here's my code:

*****************************************************

'Date Ordered results

Dim DateOrdered

'Obtain the value for DateOrdered
DateOrdered = OracleFormWindow("Sales Order").OracleTabbedRegion("Order Information").OracleTextField("Date Ordered").GetROProperty("value")

'Msgbox DateOrdered
' I used the msgbox to T/S earlier

'Create a new datasheet for DateOrdered
Variable=DataTable.AddSheet ("Results").AddParameter("DateOrdered", DateOrdered)

'Export the data from the new datasheet into an excel doc.
DataTable.ExportSheet "\\\QTP scripts\Scripts - UAT1\Key_Basic_Order_One_Multi_Lines_UAT1\Results.xls" ,"Results"

****************************************************

That works fine. But I have a few concerns:

1. When I run my script tomorrow will it overwrite the row of data that was just written to the excel document? If so how can I force QTP to check and see if that row has any data, if it does to keep checking for an emapty row then imput data there?

2. So far I have exported the data for "DateEntered". Next I will export "Customer #" into the same excel document but under a different column name. That shouldn't be a problem right? The data will come from the same script and iteration.

3. By the way, I already had this excel document created, but when QTP exported the data to the excel document it over wrote the column headers I already had in there. Why is that? It should have only created a new datasheet within QTP.

Thanks again,
Steve
Please use plain text.
Honored Contributor
Rhonda Robinson
Posts: 3,159
Registered: ‎11-13-2007
Message 6 of 9 (2,627 Views)

Re: Export QTP output data to Excel

1) Iterations within the script should increment the line number. You can check for an empty row by using a while statement saying while the value of the field in the current row is not blank, increment the row number and retrieve the value again. Once it retreives a blank value, set the row to the current row.

2) The is no problem with writing data to the same row in a different column

3) Did the Column Headers match? Were they in the same order? QTP will export exactly what it has, including its column headers.
Please use plain text.
Occasional Visitor
Siddi KrishnaPrasad
Posts: 1
Registered: ‎08-20-2009
Message 7 of 9 (2,627 Views)

Re: Export QTP output data to Excel

Use the below code:
----------------------
DataTable.ImportSheet "..\..\TestData\Input.xls",1,dtGlobalSheet
DataTable.ExportSheet "..\..\Results\Output.xls","Global"

Cheers
Please use plain text.
Frequent Advisor
goody_f9
Posts: 39
Registered: ‎01-13-2010
Message 8 of 9 (2,627 Views)

Re: Export QTP output data to Excel

Hi Rhonda Robinson

I have a similar scenario.
But i need to populate data in Excel from a dynamic Webtable.
When I populate in spreadsheet every iteration data is replacing the old data but i need old data too .
how to overcome this ??
do i need to say while the value of the field (Excel sheet field)in the current row is blank populate the data ///
Do you have any sample code >?

Thanks
Goody
Please use plain text.
Frequent Advisor
goody_f9
Posts: 39
Registered: ‎01-13-2010
Message 9 of 9 (2,627 Views)

Re: Export QTP output data to Excel

May be its good idea to create new thread ...Im creating a new thread for my question
Please use plain text.
The opinions expressed above are the personal opinions of the authors, not of HP. By using this site, you accept the Terms of Use and Rules of Participation