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

> 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 ist to Excel","Export.xls","XLS")) TYPE XLS
> Thank You

ORDER BY lndate doesn't really guarantee that the data will be sorted exactly the same way each time, or in Excel compared to VFP. ORDER BY lndate,lnnum,lcknum would be safer.
The quickest way to handle the lntotal column is in a SCAN loop through the Temp cursor, or do it in Excel.
In a VFP report it's very easy, Just check the box 'Don't show repeating values' for the colummn



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