Microsoft KB Archive/265731

= How To Automate Excel 2000 Subtotals Function in Visual FoxPro =

Article ID: 265731

Article Last Modified on 6/29/2004

-

APPLIES TO


 * Microsoft Visual FoxPro 6.0 Professional Edition

-



This article was previously published under Q265731



SUMMARY
Microsoft Excel provides a useful function called Subtotals, which you may automate with Microsoft Visual FoxPro. The Subtotals function allows you to choose groups and columns to subtotal. The following example shows how to automate the Excel 2000 Subtotals function.



MORE INFORMATION
Follow these steps to run the example:  Create a new Visual FoxPro program.  Paste the following sample code into the Visual FoxPro program created in step 1:
 * 1) DEFINE xlsum -4157

oExcel = CREATEOBJECT(&quot;Excel.application&quot;)
 * !* Create a reference to an Excel OLE object

With oExcel .application.workbooks.Add
 * !* Add a new workbook

.Visible = .T.
 * !* Make Excel visible

.Range(&quot;A1&quot;).Value = &quot;Company&quot; .Range(&quot;B1&quot;).Value = &quot;Number Sold&quot; .Range(&quot;C1&quot;).Value = &quot;Paid 30+&quot; .Range(&quot;D1&quot;).Value = &quot;Paid 60+&quot;
 * !* Add records to workbook

.Range(&quot;A2&quot;).Value = &quot;AAA&quot; .Range(&quot;B2&quot;).Value = &quot;1&quot; .Range(&quot;C2&quot;).Value = &quot;1&quot; .Range(&quot;D2&quot;).Value = &quot;0&quot; .Range(&quot;A3&quot;).Value = &quot;AAA&quot; .Range(&quot;B3&quot;).Value = &quot;2&quot; .Range(&quot;C3&quot;).Value = &quot;0&quot; .Range(&quot;D3&quot;).Value = &quot;1&quot; .Range(&quot;A4&quot;).Value = &quot;BBB&quot; .Range(&quot;B4&quot;).Value = &quot;3&quot; .Range(&quot;C4&quot;).Value = &quot;1&quot; .Range(&quot;D4&quot;).Value = &quot;0&quot; .Range(&quot;A5&quot;).Value = &quot;BBB&quot; .Range(&quot;B5&quot;).Value = &quot;4&quot; .Range(&quot;C5&quot;).Value = &quot;1&quot; .Range(&quot;D5&quot;).Value = &quot;0&quot;

.Range(&quot;A1:D5&quot;).Select
 * !* Select cells

oSelected = .Selection

EndWith

COMARRAY(oSelected, 11)
 * !* Insure array is 1 based

LOCAL ARRAY laArray(2) laArray(1) = 2 laArray(2) = 4
 * !* Create a FoxPro array to hold columns to be subtotaled
 * !* Choose columns two and four to subtotal

oSelected.Subtotal(1, xlsum, @laArray, .T., .F., .T.)  Save and run the program.
 * !* Call the subtotal function

Results

You will note the following:
 * An Excel worksheet is created.


 * The worksheet is populated with data.


 * Visual FoxPro creates an array and fills it with the columns you want to subtotal.


 * The Subtotal function runs, which sums columns two and four.

