Dataworks Enterprise Office Integration

Dataworks Enterprise Office Integration consists of module design to allow Excel and Word to publish and subscribe to data through Dataworks Enterprise.

Excel

The Excel Add-In (TOSC RTCache.xla) is capable of both importing and exporting data using OLE automation. OLE automation is significantly more efficient than using traditional DDE approaches to similar problems. Import and Export is achieved using the functions RTImport() and RTExport(). These functions are embedded directly into the formula cells of the sheet. This is suitable for use on new spreadsheets. However, where the spreadsheets already exist, the add-in also provides an alternative export facility using Dataworks Enterprise DDE Client software.

Spreadsheet Import

RTImport() is a function which can be applied to spreadsheet cells or arrays. Where there is a lot of data to be import, the array approach is more efficient. Two spreadsheets are provided on the distribution CD to illustrate these two approaches using the same data set. Currencies.xls imports data on a cell-by-cell basis, CurArray.xls on a array basis. The function takes three arguments, the name of the source and instrument and a variant containing the field or fields to be imported.

For instance, the formula

	=RTImport(‘SelectFeed’, ‘EUR=’, ‘BID’)

would import the Euro BID field from SelectFeed into the current cell. Any of the arguments can be substituted for with cell names or addresses. In the case of the example sheet, we are importing the values using other fields to hold the names of source instruments and fields. This capability also enables us to import on a row by row basis. Suppose that we created a row of field names to be imported. This range could be provided as a third argument and the formula could be used as an array formula populating an entire row of the spreadsheet at a time. The following formula is common:

	{=RTImport($A$1, $A4, $B$3:$I3)}

where $A$1 is the name of source, $A4 the name of the instrument and $B$3:$I$3 is a range representing field names. This is particular convenient since it allows tables to be set up containing a column of item names and a row of field names and the sheet to be updated using simple dragging of cells.

Updates to cells using RTImport() are generated using a timer. Each time the timer fires (every three seconds or so), the add-in will update all the cells in the table that contain the function assuming automatic recalculation has been selected. In many cases, the timer is a better approach than real-time update as it is more deterministic in performance terms and therefore more predictable.

NOTE: Excel does not provide a mechanism to indicate that an invariant function has changed value on a cell-by-cell basis. An invariant function is a function whose arguments are constant. Excel assumes that given a set of constant arguments to a function, calls to that function will always return the same result. Excel optimises invariant functions by not recalculating the cell value, since it already knows the result. Invariant functions can be marked ‘volatile’ to mean that if there is any change to the sheet all cells containing the function need to be recalculated. This is akin to a function like Now() where if anything updates, all cells containing the Now() formula will also update. This is more time consuming than the use of a simple timer.

Spreadsheet Export

The Excel add-in also provides a spreadsheet export facility to allow the sheet to publish data directly using Dataworks Enterprise Publishing System. There are two means to export data from a spreadsheet, the RTExport() function and Dataworks Enterprise DDE Client.

RTExport() uses an OLE automation approach to publish data. Like RTImport, the export function can be used on a cell or row basis, using much the same mechanisms. The RTExport() function takes four parameters, the first three being the same as RTImport and the last one or more variant values for the fields being exported. The example spreadsheet, Publish.xls illustrates its use. Since spreadsheet publishing is likely to be a small-scale affair, RTExport() exposes the source as a publishing source, assuming the use of Dataworks Enterprise Publishing System. RTExport() returns the same values as put in, so that the function can easily be embedded into a sheet.

The Components of Dataworks Enterprise DDE System

The add-in also provides toolbar buttons that allow sections of spreadsheet to be exported using DDE via Dataworks Enterprise DDE client. This is mainly useful where the spreadsheets are legacy applications. The RTExport() function relies on embedding the results of the export into the cells of the calculations. For legacy sheets it may be better to use Dataworks Enterprise DDE Client. The add-in provides a pair of toolbar buttons to allow a selection from a spreadsheet to be exposed as a collection of instruments or pages via the DDE client.

Word Add-In

The word add-in (PubDoc.dot) is a simple template containing VBA code that allows a Word document to be published as a series of real-time pages. The accompanying example PubDoc.doc explains how it works in detail. To use the add-in, select Templates and Add-Ins from the Tools menu and import the add-in. A button should appear on the toolbar. Pressing this button will prompt for a source and item name. On pressing OK, the template scans the current document splitting it into 25x80 pages and publishing those pages with links between them. The source is always mounts as a publishing source and the output pages marked permanent so they can be stored by a publishing server even when the Word document has been closed.

History

This component was part of the first release of the product.

Dependencies

This component depends on the installation of Office applications such as Excel and Word.

Configuration

Configuration of this component is simply to add it to the list of Add-Ins in Excel or to reference the appropriate document template in Word. The add-ins are installed in the Primark\Platform\Data area of the installation.

Known Problems

None.

Futures*

*DISCLAIMER: The changes are only possible proposals and represent neither "work in progress" nor work that is scheduled for a future release. Details of product roadmaps and current development shoudl be directed through the normal reporting channels.