James
You are filling the sheet one row at a time, column by column and this
can be VERY slow. I vaguely recall that there should be a way of
filling a range of cells at "one fell swoop" but I can't for the life of
me remember the details. I would strongly urge you to look into it
though. If you get nowhere, I would be happy to try to dig out
something for you but it might take me two or three days ....
Paul
On 01/09/2010 17:04, James E Harvey wrote:
> I'm trying to convert the "hip_no" field from a character to numeric value
> with no decimals in the resulting excel file.
>
> I ran an excel macro after the file was created and came up with this, but I
> can't figure how to do this in vfp programmatically?
>
> Selection.NumberFormat = "0"
>
> Here is the code to create the cursor and run to excel.
>
>
>
> select ;
> "/images/photo/" + ALLTRIM(filepath) + ".jpg" as photo, ;
> "/images/pedigree/" + ALLTRIM(filepath) + ".pdf" as pedigree, ;
> "/images/video/" + ALLTRIM(filepath) + ".rm" as videofile, ;
> "/images/video/" + ALLTRIM(filepath) +
> ICASE(yrfoal="2007",".rm",yrfoal="2008",".flv",yrfoal="2009",".html"," ")
> as video, ;
> COMP_NO as tattoo, ;
> name, ;
> color, ;
> SEX, ;
> GAIT, ;
> iif(DATE_FOAL<> {}, dtoc(DATE_FOAL),dtoc({})) as DATE_FOAL, ;
> SIRE1 as sire, ;
> DAM1 as dam, ;
> HIP_NO, ;
> LOC_SOLD as sale, ;
> iif(DATE_SOLD<> {}, dtoc(DATE_SOLD),dtoc({})) as DATE_SOLD, ;
> price_sold as sale_price, ;
> alltrim(buy_name) as buyer ;
> from lcfile3 ;
> order by ;
> DATE_FOAL desc, DAM1 ;
> into cursor lcfile4
>
>
> oexcel = createobject("excel.application")
> obook = oexcel.workbooks.add()
> orange = oexcel.activesheet.range("a1:p1")
>
>
> with oexcel.activesheet
> .range("a1") = "Photo"
> .range("b1") = "Tattoo"
> .range("c1") = "Name"
> .range("d1") = "Color"
> .range("e1") = "Sex"
> .range("f1") = "Gait"
> .range("g1") = "Date Foal"
> .range("h1") = "Sire"
> .range("i1") = "Dam"
> .range("j1") = "Hip No."
> .range("k1") = "Pedigree"
> .range("l1") = "Video"
> .range("m1") = "Sale"
> .range("n1") = "Date Sold"
> .range("o1") = "Sale Price"
> .range("p1") = "Buyer"
>
>
> orange = orange.offset(1,0)
>
>
> scan
>
> nlastrow = orange.row-1
> ntotalrow = nlastrow+1
>
> with orange
> .columns[1].value = photo
> .columns[2].value = [="] + alltrim(tattoo) +["]
> .columns[3].value = name
> .columns[4].value = color
> .columns[5].value = SEX
> .columns[6].value = GAIT
> .columns[7].value = DATE_FOAL
> .columns[8].value = sire
> .columns[9].value = dam
> .columns[10].value = HIP_NO
> .columns[11].value = pedigree
> .columns[12].value = video
> .columns[13].value = sale
> .columns[14].value = DATE_SOLD
> .columns[15].value = sale_price
> .columns[16].value = buyer
> endwith
>
> orange = orange.offset(1,0)
>
> endscan
>
> James E Harvey
> Hanover Shoe Farms, Inc.
> M.I.S./Corresponding Officer
> Off: 717-637-8931
> fax: 717-637-6766
> email: [email protected]
>
>
>
[excessive quoting removed by server]
_______________________________________________
Post Messages to: [email protected]
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message:
http://leafe.com/archives/byMID/profox/[email protected]
** All postings, unless explicitly stated otherwise, are the opinions of the
author, and do not constitute legal or medical advice. This statement is added
to the messages for those lawyers who are too stupid to see the obvious.
