I am using a Live Office query against a Crystal Report, with parameters. The parameters are set via Excel cells. Everything is set up and running fine, except for the following problem:
I have Live Office data in cell A1, and in cell A2 I put the word “TEST”, when I refresh the L.O. query with a new parameter and get more rows of data, the word “TEST” is pushed down to be below the new data. The real problem though, is if I have a reference to A2 in cell B2, the reference is pushed down the same as “TEST”.
That is, cell B2 starts out as “=A2” (“TEST”). After refreshing the L.O. query and getting, say, 5 more rows of data, B2 becomes “=A7”.
As a result of this issue, I’m not able to reference my Live Office data in Excel or in Xcelsius.
This issue is corrected with FP 1.8 for Live Office for XI 3.1. Adds the option “Update formulas to include/exclude cells when refreshing”. UNchecking this option corrects the problem.
You should never have cells below any LO objects in your Excel spreadsheet. When LO refreshes and more records are returned then it will insert new rows, not overwrite existing empty rows.
What I normally do is to estimate the max number of records to be returned. Then I use that in my formulas etc. which depends on the row number.
(BOB member since 2009-09-08)
(BOB member since 2006-10-09)