vfp ole automation wit Subtotals..

I am trying to automate an excel file generation from VFP9  that will have subtotals...

Any help ?

Pierre AmmounIT ConsultantAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Simply create empty Excel file with subtotals defined. Everything you need at VFP side is to fill data to cells. The rest is on Excel side.
Pierre AmmounIT ConsultantAuthor Commented:
The list that will be generated will be different each time, sometimes 100 rows, sometimes 300 rows...
How do I get to create subtotals on an empty sheet ?
No problem. You may either insert new lines via Automation which is slow or create sufficient number of empty rows in the sheet and delete unused ones before the saving.

Subtotals can be defined after e.g. each 50 lines. Empty lines deletion should not affect them. You can even define lines visibility based on the value without any deletion necessity.
First - the best way to understand what VFP Automation has to do is to do the task in Excel and record the operation as a Macro - totally without VFP.
Then when the Excel Macro has been created, examine it to see what VFP Automation needs to do.  
Yes, the Excel Macro will be in VBA and not VFP code, but it should point you in the right direction.

For example I just picked a very simple 1 column Excel file and recorded a Macro while telling Excel to perform sub-totalling.
The Macro VBA code result was:
Selection.Subtotal GroupBy:=1, Function:=xlCount, TotalList:=Array(1), _
        Replace:=True, PageBreaks:=False, SummaryBelowData:=True 

Open in new window

Now, what does that tell me?
It tells me that first I have to select the column (Column A) on which to run my sub-totals
And it tells me that I want the sub-total to Count the number of times a value appears and display the sub-total below the entries being sub-totaled.

So now I just have to write VFP Automation code to do the same.

Since each Excel file may have different information within it thereby requiring different sub-total parameters, I can't give you just one single answer as to what to do, but that should give you a general starting point.

You can also look at:
Google Search for:  VFP Excel Subtotal

Good Luck

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.