This site requires JavaScript to be enabled
IE BUMPER

Logo

Dear Tricentis qTest users,

If you would like to submit a Tricentis qTest ticket,

please use this support request form.

Tosca Version
IncidentLookup using list
Language
How can we help?

Solution Suggestions

Please enter your question to get suggestions.
  Help
IE BUMPER
Categories
Boolean Operators
OR or vertical bar symbol (|)
Finds a match if either the terms exist in a document (a union using sets).
AND
Finds a match if both terms exist in a document (an intersection of sets).
NOT, minus (-), or exclamation point (!)
Excludes documents that contain the term after NOT (a difference of sets).
Wildcards
Asterisk (*)
The asterisk symbol performs a multi character wildcard search.
Percent sign (%)
The percent sign performs a single character wildcard search.
Phrases
Quotation marks (")
Use quotation marks to search for an exact phrase with multiple terms.
For examples and more information look at our Knowledge Base: Advanced Search On The Support Portal

Question how to: using an array to loop through an excel spreadsheet

Dear Tosca Community,
we have a new process that we would like to automate and I would like to get some best practices on how to do it from the community.

 

  1. Open Excel (“Excel Open Workbook”)
  2. Set Active Workbook (“Excel Set Active Workbook”)
  3. Loop through each tab (“Set Active Worksheet”)
    1. Create Range
    2. Set Active Range
    3. Loop through each single line of Range
      • Compare two columns
      • If different

        Than

        • Start  SAP Workflow

          Else

        • Skip to next line

          Condition if end of range is reached stop

                Condition if all tabs have been used, stop

Process End

 


For simplification purpose, I did not include counter etc. but they need to be used for this nested loop for sure.

Some background on this. We have five sites („Site1“, etc.) that I would like to store in an array (possible via JSON) and loop through instead of using the set active worksheet five times. This number could increase and I would like to know how it works for reference purpose anyway – so I know there would be simpler ways to do it.

Secondly, the range in each tab of the excel-spreadsheet might be different. The columns are always the same, the rows however are not – is there a way to dynamically set the range? Otherwise we could live with the workaround of setting the range to 500 (or any other number that will never be reached) and just put in a condition to check whenever the last row has been found.

I did my best to simplify the spreadsheet that is used in this sub process.

All comments and thoughts are appreciated!

Thank you very much upfront & best regards,
Freddy

 

Attachments
2 0

Johannes Zwart

Created: 2017-12-21 14:35:47

Hi Freddy,

What are you testing here? What should be compared to what? What test design technique did you use to come up with this test?

What you are saying is that basically you want to program some loops in Tosca. I would not do that; it's cumbersome, hard to maintain, and doesn't utilize Tosca's strengths (which, a.o., are that you don't need to program to test your SUT).

Kind regards,

Johannes

Freddy Harnisch

2018-01-02 09:26:44

Dear Johannes,

thank you for your reply. Sorry, I noticed while reading that I missed out an important detail. Fully agreed, that for a test that would be a poor design. We are currently evaluating if we can automate a "productive" processes and we found the above meantioned somewhat representive of other processes we have and we cover most applications that would normally be used.

In terms of utilizing the strenghts of TOSCA is exactlly what I am asking for, I know loops can be created and I found some Standard Modules for JSON thatyou can use an array for. I just don't fully get the documentation on how to use JSON test cases and I hoped somebody is able to elaborate a bit more based on my above described scenario.

Regarding maintenance, for me that's always a trade-off and again for a test case I would not do it in the way described above.

Thank you very much upfront & a happy new year,
Freddy

Johannes Zwart

2018-01-05 13:32:14

Hi Freddy,

 

Thank you; happy new year to you as well!

The Excel engine support is written in VBScript in Tosca, and thus isn't the fastest nor the most flexible. So, what you want is stretching it a bit. What I would do is look into writing some custom modules that can:
- find all available worksheets
- and store them in arrays.

Using VBScript you can easily access Excel, I guess; maybe you can even reverse engineer the existing modules and use that as a basis. However, if you want to be able to use that data in the JSon XModules, I don't know exactly how to accomplish that; I haven't tried to exchange data between the 'old' and 'new' worlds in Tosca...

Unfortunately, I don't know exactly how to write custom modules in VBScript; it's probably not that hard, but up till now I only created SETs in .Net (and I don't know how to steer Excel in .Net...).

 

Kind regards,

 

Johannes

IE BUMPER

Tosca is the perfect solution

Optimize - Manage - Automate

Download Trial