Peoplecode OLE Automation

OLE (Object Linking and Embedding) enables you to exchange data into/from any application that has registered previously in your system. Frequently used to populate data into Word, Access, or Outlook. Here I’ll skip the background information about OLE, and go right into demonstrating how to export data in a Grid into an Excel spreadsheet. There are 4 peoplecode OLE functions we’ll use to implement this: CreateObject, ObjectDoMethod, ObjectGetProperty, ObjectSetProperty.
Consider the Budget Inquiry panel in PS Financials:

When user clicks the Excel button, the Grid data is transferred into the spreadsheet:

Peoplecode behind the Excel button:

     Local object &WORKAPP;
Declare Function SendClip PeopleCode D_WIN32_FUNCLIB.D_CLIP_IO FieldFormula;

&WORKAPP = CreateObject("Excel.Application");
ObjectSetProperty(&WORKAPP, "UserControl", "True");
&WORKBOOKS = ObjectGetProperty(&WORKAPP, "Workbooks");
&WORKBOOK = ObjectDoMethod(&WORKBOOKS, "Add");
&WORKSHEETS = ObjectGetProperty(&WORKBOOK, "Worksheets");
&WORKSHEET = ObjectGetProperty(&WORKSHEETS, "Item", 1);
ObjectSetProperty(&WORKSHEET, "Name", "MyOLEtest");
&RANGE = ObjectGetProperty(&WORKSHEET, "Range", "A1:E1");
&FONT = ObjectGetProperty(&RANGE, "Font");
ObjectSetProperty(&FONT, "Bold", "True");
&HEADING = "Select" | Char(9) | "Budget Period" | Char(9) |
"Begin Date" | Char(9) | "End Date" | Char(9) | "Description";&DTL_LINES = "";
For &IDX = 1 To ActiveRowCount(RECORD.CTL_CRIT_SEG) Step 1;
&SELECT = FetchValue(CTL_CRIT_SEG.SELECT_BDGT_PERIOD, &IDX);
&BP = FetchValue(CTL_CRIT_SEG.BUDGET_PERIOD, &IDX);
&BEG_DT = FetchValue(CTL_CRIT_SEG.BEGIN_DT, &IDX);
&END_DT = FetchValue(CTL_CRIT_SEG.END_DT, &IDX);
&DESCR = FetchValue(CTL_CRIT_SEG.DESCR, &IDX);
&LINE = &SELECT | Char(9) | &BP | Char(9) | &BEG_DT | Char(9) |
&END_DT | Char(9) | &DESCR;
&DTL_LINES = &DTL_LINES | &LINE | Char(10);
End-For;
&XX = SendClip(&HEADING);
ObjectDoMethod(&RANGE, "Select");
ObjectDoMethod(&WORKSHEET, "Paste");
&XX = SendClip(&DTL_LINES);
&CELL = "E" | &IDX;
&RANGE2 = ObjectGetProperty(&WORKSHEET, "Range", "A2:" | &CELL);
ObjectDoMethod(&RANGE2, "Select");
ObjectDoMethod(&WORKSHEET, "Paste");
&IDX = &IDX + 1;
&CELL = ObjectGetProperty(&WORKSHEET, "Range", "F" | &IDX);
ObjectDoMethod(&CELL, "Select");
ObjectSetProperty(&WORKAPP, "Visible", True);
ObjectDoMethod(&WORKBOOK, "SaveAs", "c:\temp\oletest.xls");

Code Dissection

Local object &WORKAPP;

**Must declare the Object before instantiating it properly

Declare Function djSendClip PeopleCode
D_WIN32_FUNCLIB.D_CLIP_IO FieldFormula;

**Use a clipboard function posted at this site earlier, this allows us to copy data into clipboard and paste to spreadsheet-same way we manually paste data. Not the only way to do it, but it simplifies the job. You can copy in the code here or in a FUNCLIB as above

&WORKAPP = CreateObject("Excel.Application");
::::::::::::::::
&WORKSHEETS = ObjectGetProperty(&WORKBOOK, "Worksheets");

**Instantiate anExcelspreadsheet,hierarchy:Application->Workbooks->Workbook->Worksheet, allow user to control it after we are done

&WORKSHEET = ObjectGetProperty(&WORKSHEETS, "Item", 1);
ObjectSetProperty(&WORKSHEET, "Name", "MyOLEtest");

**Name sheet #1 MyOLETest

&RANGE = ObjectGetProperty(&WORKSHEET, "Range", "A1:E1");
:::::::::::::::::::::
ObjectDoMethod(&WORKSHEET, "Paste");

**Set title text of the 5 columns in Bold

&DTL_LINES = "";
For &IDX = 1 To ActiveRowCount(RECORD.CTL_CRIT_SEG) Step 1;
:::::::::::::::::::::::
End-For;
&XX = SendClip(&DTL_LINES);

**Prepares the grid data & copy to clipboard

&CELL = "E" | &IDX;
&RANGE2 = ObjectGetProperty(&WORKSHEET, "Range", "A2:" | &CELL);
ObjectDoMethod(&RANGE2, "Select");
ObjectDoMethod(&WORKSHEET, "Paste");

**the data will occupy cells A2 (Row 1 is title) through column E and whatever number of rows as the value &IDX

&IDX = &IDX + 1;
&CELL = ObjectGetProperty(&WORKSHEET, "Range", "F" | &IDX);
ObjectDoMethod(&CELL, "Select");

**move cursor to a new cell

ObjectSetProperty(&WORKAPP, "Visible", True);
ObjectDoMethod(&WORKBOOK, "SaveAs", "c:\temp\oletest.xls");
**display the spreadsheet and save it

You can further define formula, subtotal, sort cells, or set up a template that includes these formulas, subtotals etc. When you populate the cells, this additional information will be readily present. This method is a simple and efficient alternative to nVision reports. With your knowledge about specific application, you can invoke different methods to achieve the same results as we did here.

(this article was originally published on 03/14/02 at www.slerp.com. PSTools version 5,6,7)