DOCUMENT:Q265731  12-MAY-2001  [foxpro]
TITLE   :HOWTO: Automate Excel 2000 Subtotals Function in Visual FoxPro
PRODUCT :Microsoft FoxPro
PROD/VER:WINDOWS:2000,6.0
OPER/SYS:
KEYWORDS:kbole kbExcel kbvfp600 kbGrpDSFox kbDSupport kbexcel2000 kbCodeSnippet

======================================================================
-------------------------------------------------------------------------------
The information in this article applies to:

 - Microsoft Visual FoxPro for Windows, version 6.0 
-------------------------------------------------------------------------------

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:

1. Create a new Visual FoxPro program.

2. Paste the following sample code into the Visual FoxPro program created in
   step 1:

   #DEFINE xlsum -4157

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

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

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

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

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

   *!*	Select cells 	
   	.Range("A1:D5").Select  

   	oSelected = .Selection

   EndWith

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

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

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

3. Save and run the program.

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.

REFERENCES
==========

For additional information about Office Automation, click the article numbers
below to view the articles in the Microsoft Knowledge Base:

   Q222101 HOWTO: Find and Use Office Object Model Documentation

   Q148474 Ole_samp.exe to Microsoft Excel, Word, & PowerPoint

   Q252615 OLE Examples for Taking Control of Excel from Visual FoxPro

Additional query words: office excel automation

======================================================================
Keywords          : kbole kbExcel kbvfp600 kbGrpDSFox kbDSupport kbexcel2000 kbCodeSnippet 
Technology        : kbVFPsearch kbAudDeveloper kbVFP600
Version           : WINDOWS:2000,6.0
Issue type        : kbhowto

=============================================================================

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS
PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND.  MICROSOFT DISCLAIMS
ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES
OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE.  IN NO
EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR
ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL,
CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF
MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE
POSSIBILITY OF SUCH DAMAGES.  SOME STATES DO NOT ALLOW THE EXCLUSION
OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES
SO THE FOREGOING LIMITATION MAY NOT APPLY.

Copyright Microsoft Corporation 2001.