Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Satyapal Singh
  Where is Satyapal Singh?
 DELHI
 India
 Satyapal Singh
 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: 58550 # Views: 9 # Ratings: 0
Version: Visual FoxPro 7 Category: Microsoft Office Automation
Date: Monday, December 27, 2004 8:30:26 AM         
   


hi. Chieh Chiu,

I am totaly new in vfp the same programe I have also written in the VFP but instead of creating a excel file I am creating txt file because I don't have any knowledge about the excel code or simply about your step two.

is it possible for you to please email me a sample code ?. I will be greatful to you.

basically I want to know how we can add the record in excel file one by one. I will give my sample to below and the same way i want in excel please help if possible for you.

set date brit
set cent on


sele a
use trans
store harp_key to hkey
r=1
sele b
use hotel
*index on htlkey tag hrp
set order to tag hrp

set devi to file sps.txt

do while !eof()
sele b
seek hkey
if found()
@ r,1 say b->htlName pict 'xxxxxxxxxxxxxx'
@ r,20 say b->htladrs1
else
@ r,1 say "Not Found"
endif
sele a
if !eof()
skip
endif
store harp_key to hkey
r=r+1

enddo
set devi to scre
modi comm sps.txt


Thanks & warm Regards
Satyapal




> 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