vfp ole automation wit Subtotals..

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

Any help ?

Pierre AmmounAsked:
Who is Participating?
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
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 AmmounAuthor 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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.