IF field to check a range of Excel cells works with blanks not fal

[BABs]

If I want for word to check a range of cells to see if they're empty or not,
this works:
{ IF {LINK Excel.Sheet.8 "\\\\Server\\data\\Info.xlsb" "Client!R2C2:R10C2"
\f4 \r } = "" "No data" "There is data available" }

However, if the cells contain true/false, why won't this work?
{ IF {LINK Excel.Sheet.8 "\\\\Server\\data\\Info.xlsb" "Client!R2C2:R10C2"
\f4 \r } = "FALSE" "All entries are FALSE" "Not all entries are FALSE" }

TIA

 

[Peter Jamieson]

{ IF {LINK Excel.Sheet.8 "\\\\Server\\data\\Info.xlsb"
"Client!R2C2:R10C2"

\f4 \r } = "" "No data" "There is data available" }

It surprises me that that would work because I think you would be
inserting a 9-row table using RTF format and I don't think that would =
"". Unless the \f4 is affecting what appears in the word document.

But that's why the second one won't work - if every cell is FALSE the
result of the LINK should be a nine-row table with FALSE in each cell,
which will not compare with a single "FALSE" text.

If you "only" need to distinguish between the case where every cell is
FALSE and all the other cases, then you could consider trying the
following (I don't know if any of them works)
a. using the \t switch instead of \f4 \r to give you a plain text
result (with paragraph markers at the end of each row)
b. either comparing against a fixed text like

"FALSE
FALSE
FALSE
FALSE
FALSE
FALSE
FALSE
FALSE
FALSE
"

e.g. something like

{ IF {LINK Excel.Sheet.8 "\\\\Server\\data\\Info.xlsb"
"Client!R2C2:R10C2" \t } = "FALSE
FALSE
FALSE
FALSE
FALSE
FALSE
FALSE
FALSE
FALSE
" "All entries are FALSE" "Not all entries are FALSE" }



(as long as you know in advance how many cells to compare) or
c. comparing against another range of cells of the same size that you
know contains FALSE in each cell, e.g.

{ IF {LINK Excel.Sheet.8 "\\\\Server\\data\\Info.xlsb"
"Client!R2C2:R10C2" \t } = {LINK Excel.Sheet.8
"\\\\Server\\data\\false.xlsb" "Falsesheet!R2C2:R10C2" \t } "All entries
are FALSE" "Not all entries are FALSE" }

However
d. I think that it likely to break down - even if it works - when the
comparison texts reach 64 or 128 characters
e. if the number of cells can vary, because recent versions of Word
tend to resolve nested fields inside LINK fields, you might find it
difficult to arrange that the range in falsesheet matches the range in
Client without using VBA to update the range. And if you're using VBA,
it might be better to go off and inspect the sheet using that, as long
as you don't run into locking problems.


Peter Jamieson

http://tips.pjmsn.me.uk

 

[BABs]

Peter thanks but the number of cells changes with every client so I'm not
sure how to go about it. I'm currently using nested IF fields, but that
becomes long and drawn out. Is there a way to use IF AND OR like in excel?

 

[Peter Jamieson]

Is there a way to use IF AND OR like in excel?

Word's facilities in this area and Excel's are at best "different".

Your code - however it is expressed - needs to make a comparison between
"a set of cells in Excel" and "<something>" and in the end, you're only
likely to be able to do that reliably in Excel itself.

As far as Word fields are concerned, another possibility might be to use
a { DATABASE } field to issue a Jet SQL query that would return a single
value from a specified set of cells, but (a) I'd have to consider
whether that can even be done and (b) attempting it introduces its own
problems.

Otherwise, I think you'll probably need VBA or some other procedural
approach.


Peter Jamieson

http://tips.pjmsn.me.uk