Excel formulas not calculating in a Devtest filter excel

[Rahul_THD]

When Devtest filters to an excel, the cells are not calculating when referencing to the cells where Devtest has written the result values.

e.g. Say Devtest has filtered JDBC SQL query results to a spreadsheet - A1:C1 and A2:C2 and if I have formulas as A3=A2; B3=B2 & C3=C2. Then cells A3, B3, C3 are not calculating automatically during dynamic execution. Is this a known problem? Do we have a solution? I have set formula calculation as automatic and cell formatting as General, but still getting the same issue. Please help!

[Legacy User]

Hi Rahul, the Read Rows from Excel File Data Set - DevTest Solutions - 9.1 - CA Technologies Documentation (pages 1653 - 1654 in the PDF version of the DevTest doco) contains a tip that states: 'The Excel spreadsheet must not contain formulas.'  There is no other comment with regard to formulas that I can find. 

 

I would interpret the statement to mean that it is expected behavior that your formulas are not going to automatically recalculate at runtime.

[Rahul_THD]

I believe this should be considered as a known issue and plan to fix in the future as this would be a very helpful feature to have while working with excel datasets! Any thoughts?

[Legacy User]

Hi Rahul_THD, my suggestion is that you create an Idea in the forum so the product team (and other members here) can consider and vote on various options to support a feature like this. 

In addition to Excel formulas, would you need a facility to execute VBA macros as well?

 

As an aside...

Keep in mind that DevTest is often installed on server environments where no MS Office components reside. DevTest consumes Excel documents as if they represent "raw" data not Excel documents.  It is one thing to open an Excel document and iterate over its content.  It is another thing to open an Excel document and cause it to execute formulas and macros without having access to the underlying MS Excel "engine" / code.  Your issue seems to stem from the latter in that you need Excel to "execute" a command whose actual execution and results are generated by the "internal" MS Excel software.  I am not even sure that COM Interop between DevTest and Excel would work if MS Office (Excel) is not installed the OS platforms that DevTest supports.  Further, executing VBAs macro without invoking Excel introduces even greater complexity.