AnsweredAssumed Answered

vrf Reading excel data - evaluating formulae How Vxl does this...

Question asked by warren.pickles on Jul 11, 2004
Greg,
I reckon that ought to work, but it returns only the first cell in the
range, when I tried it.
In the end I got VEE to loop through the fields in the record returned by
range.Value, and build up a 2D array as it goes along.
That was quick enough.
Good luck with vxl.
Warren


-----Original Message-----
From: Greg Wale [mailto:gregwale@passtechnologies.com]
Sent: 09 July 2004 16:11
To: Pickles, Warren; vrf@agilent.com
Subject: RE: Reading excel data - evaluating formulae How Vxl does
this...


To make Excel return the results of a formula, here is roughly what Vxl (for
sale at www.passtechnologies.com) does:


xl.Range(A).Select;
A=xl.ActiveCell.Formula;
xl.evaluate(A)



best regards,
Greg



> -----Original Message-----
> From: Pickles, Warren [mailto:Warren.Pickles@tycoelectronics.com]
> Sent: Thursday, July 01, 2004 1:35 AM
> Subject: RE: Reading excel data - evaluating formulae
>
> Just for the record, I spent two hours yesterday and couldn't find any
> easy
> way to do this.
> Shawn - Range.Text returns the empty string.
> Vinod - you're right Range.Value works provided there are no blank cells -
> but I have to allow for blank cells.
> I think I will have to loop through the record fields and build up my 2D
> array.  If I find a non-looping way of doing the same thing,I'll let you
> know.
> Warren
>
> -----Original Message-----
> From: Vinod [mailto:papineni_vinod@yahoo.com]
> Sent: 01 July 2004 04:21
> To: VRF
> Subject: [vrf] Reading excel data - evaluating formulae
>
>
>
> > Just take the "range", this should give you the values. Works for
> me...
>
> OK, I take that back. "range", "range.value" work as long as there is
> no unused/blank cell within the range. I think, VEE converts the
> returned array to a record when it finds 'null' element in the array.
>
> -Vinod
>
>
> --- "Pickles, Warren" <Warren.Pickles@tycoelectronics.com> wrote:
> > Hi,
> >
> > I'm reading data from an excel sheet into VEE.  I use this formula:
> > Set range = Wks.UsedRange;
> > range.formula;
> >
> > ... where Wks is a worksheet object.  The data on the worksheet end
> > up in VEE as a 2D array which is exactly what I want.  But if any
> > cell contains a formula, VEE ends up with the text of the formula
> > rather than the evaluated value.  For example, if the cell
> > contains "=2+3" then it shows up in excel as "5", and I want VEE to
> > read it in as 5, but VEE reads "=2+3".
> >
> > Anyone got a fix?  The range.value property gives evaluated formulae
> > but that gives a record with thousands of fields which is useless to
> > me.
> >
> > Thanks,
> > Warren
>
>
>
>
>
>
>
>
> __________________________________
> Do you Yahoo!?
> New and Improved Yahoo! Mail - 100MB free storage!
> http://promotions.yahoo.com/new_mail
>
> ---
> You are currently subscribed to vrf as: Warren.Pickles@tycoelectronics.com
> To subscribe send a blank email to "join-vrf@it.lists.it.agilent.com".
> To unsubscribe send a blank email to "leave-vrf@it.lists.it.agilent.com".
> To send messages to this mailing list,  email "vrf@agilent.com".
> If you need help with the mailing list send a message to
> "owner-vrf@it.lists.it.agilent.com".

---
You are currently subscribed to vrf as: rsb@soco.agilent.com
To subscribe send a blank email to "join-vrf@it.lists.it.agilent.com".
To unsubscribe send a blank email to "leave-vrf@it.lists.it.agilent.com".
To send messages to this mailing list,  email "vrf@agilent.com". 
If you need help with the mailing list send a message to "owner-vrf@it.lists.it.agilent.com".

Outcomes