AnsweredAssumed Answered

vrf Reading excel data - evaluating formulae

Question asked by warren.pickles on Jul 1, 2004
Thanks all, for your excellent suggestions.  Couldn't do it at all; now I
have two or three different solutions.  Good old vrf.

-----Original Message-----
From: FUKUI Yutaka [mailto:fukui.yutaka@jp.fujitsu.com]
Sent: 01 July 2004 13:17
To: VRF
Subject: [vrf] Re: Reading excel data - evaluating formulae


Hi,


Just another thought, but...

Copy "Range1" and paste them as value to "Range2".
Then read data from "Range2".

I modified an example program "Excel97a.vee" as "Excel97d.vee".

Please take a look.
Result array is type text.

Range0 "A2:A21" contains random values.
Range1 "B2:B21" contains formula(0 minus Range0).
Range2 "C2:C21" contains same value as Range1.


Regards,
FUKUI yutaka


Andrew Fudge wrote:
>
> Hello All
>
> Just a thought, but...
>
> Would the same loop in an Excel Macro run faster than a VEE loop.  There
was
> a recent post which describes how to insert a macro into excel (I think it
> was excel).  You could then run it and return the array output into VEE.
>
> Andrew Fudge
> Wales, UK
>
> ----- Original Message -----
> From: "Pickles, Warren" <Warren.Pickles@tycoelectronics.com>
> To: "VRF" <vrf@agilent.com>
> Sent: Thursday, July 01, 2004 8:34 AM
> Subject: [vrf] 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: AndrewF@genesysibs.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: fukui.yutaka@jp.fujitsu.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".

--
*****************************************************************
*  $BJ!0f!!K-!J (JFUKUI Yutaka)
* E-Mail: fukui.yutaka@jp.fujitsu.com
*  $BIY;NDL3t<02q<R!!@n:j9)>l (J
*  $B$b$N$E$/$j?d?JKIt!&@8;:5;=Q3+H/E}3gIt!K7WB,@_Hw3+H/It (J
*  $B7WB,@_Hw3+H/It (JHomePage http://www.psl.fujitsu.co.jp/htd2/ksk/
*  $B@n:j;TCf866h>e>.EDCf#4 (J- $B#1 (J- $B#1!!M9JXHV9f!' (J211-8588
* TEL  $B!!30@~ (J 044-754-3657, $B!!Fb@~ (J 711-3-1966,7
* FAX  $B!!30@~ (J 044-754-3509, $B!!Fb@~ (J 711-3-5359
*****************************************************************


---
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