Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Andy Smith
  Where is Andy Smith?
 Bloomfield
 New Jersey - United States
 Andy Smith
 To: Chieh Zhong
  Where is Chieh Zhong?
 Montreal
 Canada
 Chieh Zhong
 Tags
Subject: RE: Best approach of creating Excel Report From...
Thread ID: 43065 Message ID: 58705 # Views: 8 # Ratings: 0
Version: Visual FoxPro 7 Category: Microsoft Office Automation
Date: Wednesday, December 29, 2004 9:57:36 PM         
   


I think the simplest way is to take advantage of Excel's subtotaling feature.

First, set up a new report, say, ExcelRpt, whose data environment includes the Companies, Clients and Orders tables with one-to-many relations from Companies to Clients and from Clients to Orders. Then, resisting temptation to do any data grouping, put one long text field in the Detail band whose expression should be [Company.Name + "," + Client.Name + "," + Order.Item + "," + Order.Price]. All other bands should be totally blank and shrunk to zero inches high.

Then just do REPORT FORM ExcelRpt TO FILE ExcelRpt.csv ASCII, and you'll get a text file which will open in Excel when double-clicked. When you have it open in Excel, explore the Data / Subtotals command, which will do all the totals you want.

> Hi Everyone:
>
> I would like to ask your expert opinion in relates to how I creates Excel Report From VFP. Hopefully some of you might have a better way of doing it.
> Here how I do it.
>
> This is the sample type of report I have to create:
> ================================
>
> ABC Company (Company)
> John Smith (Client)
> Order Item #1 1000.00
> Order Item #2 1050.00
> Order Item #3 800.00
> Total 2850.00
>
> Peter Johnson (Client)
> Order Item #1 500.00
> Order Item #2 900.00
> Order Item #3 1500.00
> Total 2900.00
>
> Total: ABC Company 5750.00
>
> XYZ Company (Company)
> Sam Jamesh (Client)
> Order Item #1 1000.00
> Order Item #2 1050.00
> Order Item #3 800.00
> Total 2850.00
>
> Chic Pea (Client)
> Order Item #1 500.00
> Order Item #2 900.00
> Order Item #3 1500.00
> Total 2900.00
> Total: XYZ Company 5750.00
>
>
> This is how I approach this in VFP :
> ================================
> Step 1: Create Excel layout cursor, i.e.
>
> Scan through company
> add company into cursor
> scan through client belonging this this company
> add client into cursor
> add order
> add Total for this client
> endscan
> add total for this company
> endscan
>
> Step2: Create Excel object and use this command to append data into Excel sheet
>
> .Range("A1").Activate
> SELECT C_ExcelData
> GO Top
> _VFP.DataToClip("C_ExcelData", , 3) && Copy to buffer
> .ActiveSheet.Paste() && Paste the Data
>
> ..
> .. and so on..
>
> Step3: Apply formatting, bold, merge cell, etc, i.e.
> lnRowCount = .ActiveSheet.UsedRange.rows.Count
> lnColumnCount = .ActiveSheet.UsedRange.columns.Count
>
> *** Go through each row, customize the needed columns and rows
> For i = 1 to lnRowCount
> * Formatting, bolding,... .... ... etc
> endfor
>
>
> Here my questions, Is there a better way of doing this?
> Can I merge Step 1 right into Step 3, skipping step 2. which means I am scanning through cursors and
> inserting into excel rows and columns, applying formatting at the same time instead of having to create the cursor first then append and apply formating.
>
> The drawback with my method is that in all the total or sub total section, I don't have a fomula in it, adding formula after appending data is a bit tricky,
> If I have to add formula to it, I have to know how many rows that total or sub-total sections belongs to it, then use the .ActiveCell.FormulaR1C1 property
> setting to set the formula. If I can merget step 1 to step 3, then it would be much easier.
>
> Thank you.
>
> Chieh
>
>
>
>
>


ENTIRE THREAD

Best approach of creating Excel Report From VFP Posted by chieh chiu @ 5/17/2004 6:20:04 AM
RE: Best approach of creating Excel Report From... Posted by Yuri Rubinov @ 5/17/2004 3:30:26 PM
RE: Best approach of creating Excel Report From... Posted by chieh chiu @ 5/17/2004 4:18:25 PM
RE: Best approach of creating Excel Report From... Posted by Satyapal Singh @ 12/27/2004 8:30:26 AM
RE: Best approach of creating Excel Report From... Posted by DNAunion @ 12/29/2004 5:47:09 PM
RE: Best approach of creating Excel Report From... Posted by Andy Smith @ 12/29/2004 9:57:36 PM
RE: Best approach of creating Excel Report From... Posted by Raul Urquilla @ 12/31/2004 10:05:10 AM