Hi,
Erm, talked about getting data *from* excel ;D (Who needs stuffing
it???)
Anywhoo, wanted to do that in VEE instead of formatting a 30kcell
range, see my customer has a SW that produces this XLS from DB and in a
single column there lurks a description, limits and sequential data below
(won't like having multiple objects pointing to subranges either). So,
formatting the whole column would scrap it. Hey, these are results overnight
of...hmmm...lessee...a test called "98927184642177217727721"...pardon me
while I bang me head to desk...
So I ended up making a transfer array with:
transM=strPosChar(ary,",")+1, then getting string part before the comma,
adding '.' if necessary and finally pasting string part after the comma:
strFromLen(ary,0,transM-1) + clipUpper(transM,1)*"." +
strFromThru(ary,transM,strLen(ary))...Seems to work adequtely; Getting ~15k
results, saving as, closing, reading with 'from file' and deleting tempfile
takes 0.2557s altogheter and formatting commas and empty results out 0.162
more. Think I can live with that ;D
Now I just have to follow Shawn's recommendation...weekend ahoy.
Have nice one, all of you. Great place, this VRF.
BR,
Antti
> -----Original Message-----
> From: Shawn Fessenden [mailto:shawn@testech-ltd.com]
> Sent: 11. keskuuta 2004 14:08
> To: VRF
> Subject: [vrf] RE: Masses of data from Excel... FW: arbot
>
>
> This is the original message. The attachment transfers a test
> array to an
> Excel Range whose shape varies to demonstrate what goes
> where. The reverse
> operation applies equally. An Excel Range of whatever shape can be
> transferred to VEE as efficiently as possible (considering
> IDispatch) using
> an array.
>
> If the shape of the VEE array is not going to match the shape
> of the Excel
> Range, that's when there's trouble. The best thing to do is
> modify the shape
> of the Excel Range to match that necessary in VEE (i.e. when
> the data is
> entered in the sheet). The second best thing to do is to read
> the Range as
> it is and use VEE's array transformations to modify the shape.
>
> This covers only the first of the methods I mentioned:
> transferring data
> over IDispatch. I'll see if I can find 50M of data somewhere
> and try some
> stuff out. I'm pretty sure the largest data sets I have are a
> lot smaller
> though.
>
> JUST RECEIVED NEXT MESSAGE ON VRF:
>
> > just were working on through-file trick
>
> Ok, then that's settled
>
> > my locales are using comma as decimal separator and that
> > does some funny stuff
>
> Ah yes. That would be a problem! You know, I think there's a
> way to override
> that with Format. I'm not real fond of Format, but it
> definitely comes in
> handy in lots of situations. There are some Excel wizards
> around here. Greg
> Wale is one of 'em. I'm pretty sure you can select an entire
> range and apply
> a custom format in an instant.
>
> > my head is not as it used to be ;D
>
> Oh yeah! Brain mush. I recommend some alcohol (dosage varies)
> & music, and
> some dancing if you go in for that sort of thing. Follow with
> sleep and
> you'll be good as new.
>
> ---
>
> >From = Shawn Fessenden
> Date = 12/25/2003 10:04:32 PM
> Subject = [vrf] RE: VEE to Excel
> MessageID = 0000000011C77702B4B2CB4A9FD20D99053FF730244F2E00
> Attachments = Excel.vee (4.6KB), footer.txt (530B)
>
> > the order of the rows are mix up.
>
> When you enter data cell by cell, the ActiveCell gets the
> datum transferred.
> In the VEE example Excel97a.vee, they're explicitly
> addressing a destination
> cell (singular) with the Cells method of the Worksheet object
> (which returns
> a Range object).
>
> When transferring an array (a much more efficient data
> transfer mechanism
> considering the IDispatch transport medium), data are transferred in
> row-major order and the shape of the destination Range
> controls what goes
> where. In the attached example, different Range shapes
> produce different
> results according to the size and shape of the source array vs. the
> destination Range.
>
> [NOTE: 06/11/2004 - I don't know if the reverse is true: if
> you define a VEE
> array with a certain size and shape and read an Excel Range
> into it, I have
> no idea what goes where or if it would even work. I tend to think it
> wouldn't work in the first place.]
> -SHAWN-
>
Please note: This e-mail may contain privileged and confidential information
and is intended solely for the addressee. If you are not the intended
recipient or an employee responsible for delivering this message to the
intended recipient, you are hereby notified that any review, publication,
dissemination, distribution, copying or use of the information in this
message is strictly prohibited. If you have received this e-mail by error,
please notify the sender immediately by replying to the message and
subsequently delete it from your system.
E-mail is susceptible to data corruption, interception, unauthorized
amendment, tampering and viruses. We only send and receive e-mail messages
on the basis that we are not liable for any such corruption, interception,
amendment, tampering, viruses, any other reprehensible acts or any
consequences of the aforementioned.
---
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".
Erm, talked about getting data *from* excel ;D (Who needs stuffing
it???)
Anywhoo, wanted to do that in VEE instead of formatting a 30kcell
range, see my customer has a SW that produces this XLS from DB and in a
single column there lurks a description, limits and sequential data below
(won't like having multiple objects pointing to subranges either). So,
formatting the whole column would scrap it. Hey, these are results overnight
of...hmmm...lessee...a test called "98927184642177217727721"...pardon me
while I bang me head to desk...
So I ended up making a transfer array with:
transM=strPosChar(ary,",")+1, then getting string part before the comma,
adding '.' if necessary and finally pasting string part after the comma:
strFromLen(ary,0,transM-1) + clipUpper(transM,1)*"." +
strFromThru(ary,transM,strLen(ary))...Seems to work adequtely; Getting ~15k
results, saving as, closing, reading with 'from file' and deleting tempfile
takes 0.2557s altogheter and formatting commas and empty results out 0.162
more. Think I can live with that ;D
Now I just have to follow Shawn's recommendation...weekend ahoy.
Have nice one, all of you. Great place, this VRF.
BR,
Antti
> -----Original Message-----
> From: Shawn Fessenden [mailto:shawn@testech-ltd.com]
> Sent: 11. keskuuta 2004 14:08
> To: VRF
> Subject: [vrf] RE: Masses of data from Excel... FW: arbot
>
>
> This is the original message. The attachment transfers a test
> array to an
> Excel Range whose shape varies to demonstrate what goes
> where. The reverse
> operation applies equally. An Excel Range of whatever shape can be
> transferred to VEE as efficiently as possible (considering
> IDispatch) using
> an array.
>
> If the shape of the VEE array is not going to match the shape
> of the Excel
> Range, that's when there's trouble. The best thing to do is
> modify the shape
> of the Excel Range to match that necessary in VEE (i.e. when
> the data is
> entered in the sheet). The second best thing to do is to read
> the Range as
> it is and use VEE's array transformations to modify the shape.
>
> This covers only the first of the methods I mentioned:
> transferring data
> over IDispatch. I'll see if I can find 50M of data somewhere
> and try some
> stuff out. I'm pretty sure the largest data sets I have are a
> lot smaller
> though.
>
> JUST RECEIVED NEXT MESSAGE ON VRF:
>
> > just were working on through-file trick
>
> Ok, then that's settled
>
> > my locales are using comma as decimal separator and that
> > does some funny stuff
>
> Ah yes. That would be a problem! You know, I think there's a
> way to override
> that with Format. I'm not real fond of Format, but it
> definitely comes in
> handy in lots of situations. There are some Excel wizards
> around here. Greg
> Wale is one of 'em. I'm pretty sure you can select an entire
> range and apply
> a custom format in an instant.
>
> > my head is not as it used to be ;D
>
> Oh yeah! Brain mush. I recommend some alcohol (dosage varies)
> & music, and
> some dancing if you go in for that sort of thing. Follow with
> sleep and
> you'll be good as new.
>
> ---
>
> >From = Shawn Fessenden
> Date = 12/25/2003 10:04:32 PM
> Subject = [vrf] RE: VEE to Excel
> MessageID = 0000000011C77702B4B2CB4A9FD20D99053FF730244F2E00
> Attachments = Excel.vee (4.6KB), footer.txt (530B)
>
> > the order of the rows are mix up.
>
> When you enter data cell by cell, the ActiveCell gets the
> datum transferred.
> In the VEE example Excel97a.vee, they're explicitly
> addressing a destination
> cell (singular) with the Cells method of the Worksheet object
> (which returns
> a Range object).
>
> When transferring an array (a much more efficient data
> transfer mechanism
> considering the IDispatch transport medium), data are transferred in
> row-major order and the shape of the destination Range
> controls what goes
> where. In the attached example, different Range shapes
> produce different
> results according to the size and shape of the source array vs. the
> destination Range.
>
> [NOTE: 06/11/2004 - I don't know if the reverse is true: if
> you define a VEE
> array with a certain size and shape and read an Excel Range
> into it, I have
> no idea what goes where or if it would even work. I tend to think it
> wouldn't work in the first place.]
> -SHAWN-
>
Please note: This e-mail may contain privileged and confidential information
and is intended solely for the addressee. If you are not the intended
recipient or an employee responsible for delivering this message to the
intended recipient, you are hereby notified that any review, publication,
dissemination, distribution, copying or use of the information in this
message is strictly prohibited. If you have received this e-mail by error,
please notify the sender immediately by replying to the message and
subsequently delete it from your system.
E-mail is susceptible to data corruption, interception, unauthorized
amendment, tampering and viruses. We only send and receive e-mail messages
on the basis that we are not liable for any such corruption, interception,
amendment, tampering, viruses, any other reprehensible acts or any
consequences of the aforementioned.
---
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".
It works the same way - just turn the assignments around.
> So, formatting the whole column would scrap it.
Ah, Range misunderstanding: a Range doesn't have to be a column, row or
square or rectangle - or even contiguous. A Range can be just a group of
cells anywhere. Try it - Ctrl-Click a bunch of different cells anywhere on
the sheet and name it MyRange. Then click A1. Then command Select MyRange.
Presto, all the previously selected cells are now selected again.
The trick is in constructing the discontinuous range via Automation. I must
confess that at the moment I don't know how that would work out, but here's
the macro I just recorded to do so, commented with clicks:
' Select a bunch of cells.
Range("A1,B3,D9,A10,C6").Select
' Click in the "NameBox".
Range("C6").Activate
' Type "MyRange" and enter.
Application.Goto Reference:="MyRange"
' Select a random cell. After this B1 is the only selected cell.
Range("B1").Select
' Click in the "NameBox", type MyRange and enter.
Application.Goto Reference:="MyRange"
' The range (A1,B3,D9,A10,C6) is now selected.
> Think I can live with that ;D
Yer right! If it works, don't fix it
next upgrade maybe.
> Now I just have to follow Shawn's recommendation...weekend ahoy.
Going to Blues Fest today downtown. I predict Awesome Performances dedicated
to the memory of Ray Charles. If anybody is around Chicago, make *sure* you
get down to Grant Park sometime in the next three days. It'll be soggy, but
after the first couple beers that won't matter.
-SHAWN-
---
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".