UFT API How to Write Web Service Response to Excel
The other day I received an e-mail from my blog-reading friend Samar. He asked:
“I’ve created a local Excel table with one column (e.g Column1). How can I set a column programmatically?”
Rather than sending him a long e-mail reply, I’ve decided to answer in the form of a blog post, which follows below.
How to Programmatically Write to Excel 2010
1. First, start the HP Flight sample application and enter the end point http://localhost:24240/HPFlights_SOAP?WSDL to import the flight web services into Service Test
(For step-by-step instructions, be sure to check out my post, HP Service Test 11 – How to Test a Web Services Video).
2. Drag the CreateFlightOrder operation onto the main canvas area.
3. Drag a Custom Code activity onto the main canvas area.
4. Under the Custom Code’s “Property Sheet,” create an Input value named joePrice.
5. Click on the “Event” icon and create a default handler for the ExecuteEvent.
6. Enter the following code under your onExecuteEvent under the TestUserCode.cs section:
7. Run the script. You should now have a price value in your Excel file:
How to Use Service Test’s Built-in Database Commands to Write to an Excel File
1. Follow steps 1 through 3 (above).
2. Drag a Concatenate String operation onto the main canvas area.
3. Under the ‘Concatenate Strings’ “Property Sheet,” enter the following under the Prefix “Property”:
INSERT into [Sheet1$] (COLUMN1) values (‘
For the Suffix, click on the “link to data source,” and point the TotalPrice value from the CreateFlightOrder operation:
4. Drag another Concatenate String operation onto the main canvas area.
For the Prefix, click on the “link to data source,” and point to the previous Concatenate String’s Result output.
For the Suffix, enter ‘).
5. Drag an Open Connection operation onto the main canvas area.
6. Under the Open Connection “Property Sheet,” enter the following under the Connection String:
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=’D:\STDEMO.xlsx’;Extended Properties=Excel 12.0;Persist Security Info=False
(FYI – IF you do not have the ACE ODBC driver you should be able to get it by installing http://www.microsoft.com/download/en/details.aspx?id=13255
7. Drag an Execute Command operation onto the main canvas area.
For the Connection property, point to the Open Connection’s “Connection Output” property.
For the Command Property, point to the previous Concatenation String’s “Results” property.
8. Run the script. You should now have a price value in your Excel file.
Hope this post helps you to excel with Excel and HP Services Test 11.20
Joe Colantonio is the founder of TestGuild, an industry-leading platform for automation testing and software testing tools. With over 25 years of hands-on experience, he has worked with top enterprise companies, helped develop early test automation tools and frameworks, and runs the largest online automation testing conference, Automation Guild.
Joe is also the author of Automation Awesomeness: 260 Actionable Affirmations To Improve Your QA & Automation Testing Skills and the host of the TestGuild podcast, which he has released weekly since 2014, making it the longest-running podcast dedicated to automation testing. Over the years, he has interviewed top thought leaders in DevOps, AI-driven test automation, and software quality, shaping the conversation in the industry.
With a reach of over 400,000 across his YouTube channel, LinkedIn, email list, and other social channels, Joe’s insights impact thousands of testers and engineers worldwide.
He has worked with some of the top companies in software testing and automation, including Tricentis, Keysight, Applitools, and BrowserStack, as sponsors and partners, helping them connect with the right audience in the automation testing space.
Follow him on LinkedIn or check out more at TestGuild.com.
Related Posts
Look, I’ve been doing this testing thing for over 25 years now. I first wrote about the AI “three waves” […]
What’s New in Low-Code/No-Code Testing for 2026 A lot had changed on the The low-code/no-code testing space and it has […]
I want to share an update on one of my favorite exploratory testing tools that just keeps getting better. I […]
Here is my list of the best open source API testing tools for 2025 Application Programming Interface (API) testing is […]