How To Use OLE Automation to Add Data to Excel Sheet

This article was previously published under Q142193
This article has been archived. It is offered "as is" and will no longer be updated.
OLE Automation provides a way for Visual FoxPro for Windows to interactwith other OLE-compliant applications. Using OLE, the Visual FoxPro programcan start a Microsoft Excel session, open a workbook, select a worksheetfrom the workbook, place data into a cell or range of cells, print aworksheet, save the updated workbook, and close the Microsoft Excelsession. This article shows by example how to do just that.

Step-by-Step Example

  1. Open a new workbook in Microsoft Excel 5.0 or Microsoft Excel 7.0 (part of Office 95) or Microsoft Excel 97 (part of Office 97), 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
  6. In Microsoft Visual FoxPro for Windows, create the following program named oleExcel.prg:
    *** Program oleExcel.prg *****#DEFINE xlLandscape 2#DEFINE xlDoNotSaveChanges 2DIMENSION laDemoData(4)      && For some Visual FoxPro users, datalaDemoData(1)= 1024          && could come from table or viewlaDemoData(2)= 2048laDemoData(3)= 5120laDemoData(4)= "The Sample Message"loExcel = CREATEOBJECT("Excel.application") WITH loExcel   .Application.Workbooks.Open("C:\XLSheets\testbook.xls")   .Application.Worksheets("Sheet3").Activate  && Select sheet      .Range("b2").Value = 14444          && constants as data   .Range("b3").Value = 25555   .Range("b4").Value = 30001   .Range("b6").Value = laDemoData(1)  && variables as data   .Range("b7").Value = laDemoData(2)   .Range("b8").Value = laDemoData(3)   .Range("a12").Value = laDemoData(4)   .Worksheets("Sheet3").PageSetup.Orientation = xlLandscape   .ActiveSheet.PrintOut  && Example of printing the worksheet   .Visible = .t. && See the results   =MESSAGEBOX("Click OK to close Excel.") && Keep the program running,                                            && so Excel isn't stranded   .ActiveWindow.Close(xlDoNotSaveChanges)   .Quit()ENDwith					
Examine the Microsoft Excel Help topic "Application Object" and the manytopics that are introduced in the Properties and Methods lists of thattopic.

For more information about many of the concepts in the example program,please see the following articles in the Microsoft Knowledge Base:
139051 How To Select Cells in Microsoft Excel by Using OLE Automation
132535 PRB: Releasing Object Variable Does Not Close Microsoft Excel
138398 PRB: Release may not Remove OLE Objects from Memory
Interoperability printing

Article ID: 142193 - Last Review: 12/04/2015 12:55:55 - Revision: 3.3

Microsoft Visual FoxPro 3.0 Standard Edition, Microsoft Visual FoxPro 3.0b Standard Edition, Microsoft Visual FoxPro 5.0 Standard Edition, Microsoft Visual FoxPro 6.0 Professional Edition

  • kbnosurvey kbarchive kbcode kbhowto kbinterop KB142193