Documentation/How Tos/Calc: DDE function

CALC FUNCTIONS

Spreadsheet Functions

< Previous Section

Next Section >

FUNCTIONS

Spreadsheet Lookup functions

• Address
• Choose
• Hlookup
• Index
• Indirect
• Lookup
• Math
• Offset
• Vlookup

Spreadsheet Information functions

• Areas
• Column
• Columns
• Errortype
• Info
• Row
• Rows
• Sheet
• Sheets

Other functions

• Dde
• Hyperlink
• Style

< Previous Page

Next Page >

DDE

Returns data from a DDE-based link.

Syntax:

DDE(server; file; item; mode)

server is the (text) name of a server; for example "soffice" for OpenOffice.org.

file is the (text) complete name or IRL (Internet Resource Locator) of the document containing the data; for example "c:\office\document\test1.ods" or "file:///documents/test2.ods".

item is the (text) name of the item to return. For example "Sheet1.B3" is cell B3 on Sheet1 of a Calc spreadsheet; "MyRange" is the name of a range in a Calc spreadsheet; "MyItem" is the name of a section or table in a Writer document.

mode is an optional parameter specifying how numbers are returned. Non-numeric text is returned unchanged.

0 or omitted: Data is converted to number if possible, using the default cell style

1: Data is converted to number if possible, in US English (en_US) format (for example with “.” as the decimal separator)

2: Data is always retrieved as text (not converted to number).

DDE creates a link to (and returns the contents of) item, so any change to item is mirrored by the DDE result.

An alternative method to create a DDE link is to copy the cell or range from the source file to the clipboard, then use the menu command Edit - Paste Special, selecting the 'Link' option. The link is inserted as a DDE function, as an array formula.

The menu command Edit - Links... lists DDE links, and allows them to be modified, manually updated or broken.

DDE stands for "Dynamic Data Exchange," which is a predecessor of OLE, "Object Linking and Embedding". DDE objects are linked by reference to the source; they are not embedded in the target document.

Examples:

=DDE("soffice";"C:\OpenOffice\test1.ods";"Sheet1.C7")

creates a link to and returns the contents of cell C7 on Sheet1 of the Calc file test1.ods.

=DDE("soffice";"C:\OpenOffice\test2.odt";"Table1")

when entered as an array formula, creates a link to and returns the contents of the table named Table1 in the Writer file test2.odt, in a single column. Each paragraph in a table cell appears in its own cell. The contents of the top left table cell appear first, followed in order by other cells on the top row, and followed similarly by other rows in order. The INDEX function can be used to return just part of the table.

=DDE("soffice";"C:\OpenOffice\test2.odt";"Section1")

when entered as an array formula, creates a link to and returns the contents of the section named Section1 in the Writer file test2.odt, in a single column, one paragraph per cell. When entered normally (not as an array formula) this returns the first paragraph of Section1.

=INDEX(DDE("soffice";"C:\OpenOffice\test2.odt";"Section1");2)

when entered as an array formula, creates a link to and returns the contents of the second paragraph of Section1 in the Writer file test2.odt.

Issues:

• Excel does not have this function.
• When using an array to return data, the size of the array is fixed on first calculation. If for example the number of lines in a Writer table or section changes, the array size in Calc does not change.
• On the majority of platforms, accented characters are treated wrong.
• There are alternative ways to link to external data.