HOWTO: Using OLE Automation to Add Data to Excel Sheet

• Microsoft Visual FoxPro for Windows, versions 3.0, 3.0b, 5.0

SUMMARY

OLE Automation provides a way for Visual FoxPro for Windows to interact with other OLE-compliant applications. Using OLE, the Visual FoxPro program can start a Microsoft Excel session, open a workbook, select a worksheet from the workbook, place data into a cell or range of cells, print a worksheet, save the updated workbook, and close the Microsoft Excel session. This article shows by example how to do just that.

MORE INFORMATION

Step-by-Step Example

1. Open a new workbook in Microsoft Excel 5.0 or Microsoft Excel 7.0 (part of Office 95), so that the program example in this article will work.

2. Select sheet 3 by clicking the tab at the bottom of the worksheet, and enter the following data into the spreadsheet:

         Enter         In Cells
         ----------------------------
         Part #        A1
         Sales YTD     B1
         1             A2 through A4
         2             A5 through A7
         3             B2 through B7
   
   

3. On the Microsoft Excel Data menu, click Subtotal. The resulting dialog box should show that at each change in Part #, the function 'SUM' adds the subtotal to 'Sales YTD' and display that summary below the data. If so, click OK.

4. Click the tab at the bottom of the worksheet for Sheet 1 to restore the workbook's default page to Sheet 1.

5. Save the workbook. Then close the workbook, and exit from Microsoft Excel. For the following example code, the workbook has been saved as

         C:\XLSheets\Testbook.xls.
   

6. In Microsoft Visual FoxPro for Windows, create the following program named oleExcel.prg:

         *** Program oleExcel.prg *****
         MyF12=ON("key",'F12')        && Save the status of F12
         ON KEY LABEL F12 DO Wrapup   && Cleanup, closeup procedure
         DIMENSION laDemoData(4)      && For some Visual FoxPro user's data
         laDemoData(1)= 1024          && could come from table or view
         laDemoData(2)= 2048
         laDemoData(3)= 5120
         laDemoData(4)= "The Sample Message"
   
         oleExcel = CREATEOBJECT("Excel.application")  && The object ;
         *                                && is 'oleExcel.application'
         oleExcel.application.workbooks.open("C:\XLSheets\testbook.xls")
         oleExcel.application.worksheets("Sheet3").activate  && Select sheet
         oleExcel.range("b2").value = 14444  && constants as data
         oleExcel.range("b3").value = 25555
         oleExcel.range("b4").value = 30001
         oleExcel.range("b6").value = laDemoData(1)  && variables as data
         oleExcel.range("b7").value = laDemoData(2)
         oleExcel.range("b8").value = laDemoData(3)
         oleExcel.range("a12").value = laDemoData(4)
         oleExcel.worksheets("Sheet3").pagesetup.orientation = 2
         && = 'xlLandscape'
         oleExcel.activesheet.printout  && Example of printing the worksheet
         oleExcel.visible = .t. && See the results
         READ EVENTS  && Keep the program running, so Excel isn't stranded
   
         * To do the following, press ALT+TAB to select Visual FoxPro from the
         * operating system's task list. Once you're back in the Visual FoxPro
         * desktop, press F12
   
         PROCEDURE Wrapup
           CLEAR EVENTS
           oleExcel.application.Activeworkbook.save && saves new subtotals,
         .
           oleExcel.quit
           && Closes Microsoft Excel and releases the oleExcel object
         .
            ON KEY Label F12 &MyF12
   
         ****** End of program ********
   
   

REFERENCES

Eamine the Microsoft Excel Help topic "Application Object" and the many topics that are introduced in the Properties and Methods lists of that topic.

For more information about many of the concepts in the example program, please see the following articles in the Microsoft Knowledge Base:

   ARTICLE-ID: Q139051
   TITLE     : How to Select Cells in Microsoft Excel by Using OLE
               Automation

   ARTICLE-ID: Q132535
   TITLE     : Releasing Object Variable does not close Microsoft Excel

   ARTICLE-ID: Q128994
   TITLE     : Behavior of GETOBJECT() with Excel and Word for Windows

   ARTICLE-ID: Q138398
   TITLE     : Release may not Remove OLE Objects from Memory

   ARTICLE-ID: Q130809
   TITLE     : KB List: OCX, OLE, ODBC, & Third-party Interoperability