HOWTO: Use OLE Automation to Add Data to Excel Sheet
ID: Q142193
|
The information in this article applies to:
-
Microsoft Visual FoxPro for Windows, versions 3.0, 3.0b, 5.0, 6.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 INFORMATIONStep-by-Step Example
- 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.
- 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
- 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.
- Click the tab at the bottom of the worksheet for Sheet 1 to restore the
workbook's default page to Sheet 1.
- 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.
- 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
Examine 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:
Q139051
HOWTO: Select Cells in Microsoft Excel by Using OLE Automation Q132535
PRB: Releasing Object Variable Does Not Close Microsoft Excel Q128994
INFO: Behavior of GETOBJECT() with Excel and Word for Windows Q138398 PRB: Release may not Remove OLE Objects from Memory
Q130809 INFO: KB List: OCX, OLE, ODBC, & 3rd-party Interoperability
Additional query words:
Interoperability printing
Keywords : kbcode kbinterop kbExcel kbVFp300 kbVFp500 kbVFp600
Version :
Platform :
Issue type : kbhowto
|