I am reading test data from excel using UFT. Cell A1 has data: =RANDBETWEEN(2,7) Cell A2 has data: =A1 Cell A2 is pointing to A1.
Each time I open excel file, I see new value is generated in A1 and then A2 displays the same. Values remain same while excel is open.
When UFT opens excel file, A1 and A2 are not same all the time. I think when UFT reads A2, it generates new value again. I like to see same value in both cells each time UFT opens an excel file. Those values should be fixed while the file is open.
I tried:
Excel > File > Option > Calculation Options > select manual > unchecked "recalculate before saving".
It did not fix the problem, UFT seems to ignore this flag.
How can I have same value in A1 and A2 each time UFT opens excel file without constantly changing the value?
You just found out that UFT fails miserably with Excel workbooks that contain formulas. Only very simple formulas are supported.
Even if RANDBETWEEN is supported (I think: it´s not), you will sooner or later discover that UFT cannot evaluate formulas except for rather trivial cases.
The recommended workaround would be: In your UFT test, if you need up-to-date results from "freshly" evaluated formulas, use the Excel automation interface to launch an Excel instance (possibly invisible), and use it to open your workbook, copy all values (not formulas) to a new workbook, and save that workbook under a different name. Then use that new workbook, which does not contain any formulars, as the data source in UFT.
That might take < 60 lines of VBScript code in UFT, and it will fix your problem forever, no matter what kind of formulas you chose to use in the future.