Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Jun Tangunan
  Where is Jun Tangunan?
 Jun Tangunan
 To: Eddie Ruiz
  Where is Eddie Ruiz?
 United States
 Eddie Ruiz
Subject: RE: export data to excel(filtering)
Thread ID: 395364 Message ID: 395366 # Views: 76 # Ratings: 2
Version: Visual FoxPro 9 SP2 Category: Databases, Tables and SQL Server
Date: Tuesday, January 14, 2014 3:50:25 AM         

> Hi im currectly using the below code in a form to export data to excel. The code works great, but would like to find out if is possible to add on my select statement that when the field 1nnum have the same data stored to only export 1 line of data on the 1ntotal field. Its hard to explain it, but if you take a look on the screenshots you might get me. If you see in the first screenshot (Exporting2ExcelScreenshoot1.jpg)the 1ntotal have and exports repetitive values and if you see on the other screenshot which is how i want it to be able to export the data the 1ntotal field only exports the first line. All I can think of is that if 1nnum have the same data stored only export 1 line of data in its 1ntotal.
> Is is possible to add this in the select statement? if soo any help will be appreciated.
> LOCAL ldStartdate, ldEnddate
> ldStartdate = thisform.startdatetextbox.value
> ldEnddate = thisform.enddatetextbox.value
> ldcustomer = thisform.customertextbox.value
> If NOT vartype(ldStartdate)+Vartype(ldEnddate)=="DD"
> Messagebox("wrong date entry")
> Return
> Endif
> If ldEnddate> Messagebox("wrong date range")
> Return
> select 1code, 1name, 1date, 1nnum, 1cknum, 1npaid, 1ntype ;
> from trans;
> WHERE 1date >= m.ldStartdate AND 1date <= m.ldenddate AND (1code = ldcustomer;
> AND CHRTRAN(1nnum, CHRTRAN(1nnum, "0123456789", ""), "") == 1nnum)and (1ntype='I');
> ORDER BY 1ndate desc;
> COPY TO (PUTFILE("Exporting List to Excel","Export.xls","XLS")) TYPE XLS
> Thank You

One way is to make TEMP cursor READWRITE. After every data is there and before exporting to xls, loop through the records and replace the repeating values with zero. Only after it is cleaned of those repeating values perform the COPY TO.



export data to excel(filtering) Posted by Eddie Ruiz @ 1/14/2014 3:42:15 AM
RE: export data to excel(filtering) Posted by Jun Tangunan @ 1/14/2014 3:50:25 AM
RE: export data to excel(filtering) Posted by Eddie Ruiz @ 1/22/2014 3:07:22 AM
RE: export data to excel(filtering) Posted by Anders Altberg @ 1/14/2014 3:06:50 PM