Documentation/Calc Functions/DDE

From The Document Foundation Wiki
Jump to navigation Jump to search
Other languages:
English • ‎Nederlands



Function name:

DDE

Category:

Spreadsheet

Summary:

Dynamic Data Exchange (DDE) is a legacy protocol that can be used to share data between applications.

The DDE function initiates a "conversation" between Calc (the "client") and another application that supports DDE (the "server"), enabling the server to pass requested data to the client as the result of the function call. Whenever the server subsequently detects a change in the data of interest, it automatically passes the update on to the client.

DDE objects are linked by reference to their source; they are not embedded in the target spreadsheet.

Syntax:

DDE(Server; Topic/File; Item/Range [; Mode])

Returns:

Returns the appropriate data from a DDE request.

Arguments:

Server is a text string (in quotation marks), or a reference to a cell containing such a text string, that is the name of a server application. The server name for LibreOffice is "soffice"; other DDE servers may be present on your system (for example, "excel" for Microsoft Excel and "winword" for Microsoft Word). However, the availability of other servers will depend on your computer system, its setup and, its contents - further discussion of this aspect is beyond the scope of this wiki page.

Topic/File is a text string (in quotation marks), or a reference to a cell containing such a text string, that is the complete name of the file containing the data of interest, including path specification. DDE does not currently support the use of relative paths in this argument.

Item/Range is a text string (in quotation marks), or a reference to a cell containing such a text string, that identifies the specific data to be returned. For example, this may be a named area or a reference to a range of cells for a Calc spreadsheet, or the name of a section or bookmark for a Writer document.

Mode is an integer value, or a reference to a cell containing that integer, that is a code to control the method by which the DDE server converts its data into numbers. It can be set to 0, 1 or 2, with the meaning assigned to each code given in the following table.

Interpretation of Mode argument
Mode Effect
0 or omitted Data converted to number if possible, using the default cell style.
1 Data converted to number if possible, in US English format (for example, with "." as the decimal separator).
2 Data is retrieved as text, with no conversion to number.
  • If any of Topic/File, Server, and Item/Range are not valid, then DDE reports a #N/A error.
  • If Mode is non-numeric, then DDE reports a #VALUE! error.
  • If Mode is a negative value, then DDE reports an invalid argument error (Err:502).
  • If Mode is a non-integer value, then it is truncated to an integer.
  • If, after truncation, Mode is greater than 2, then it is reset to the default value of 0.
  • If you open a Calc spreadsheet containing a DDE function call and Calc cannot establish a DDE conversation for the specified arguments, then a warning dialog is displayed.

Additional details:

Note pin.svg

Tip:
Calc provides several alternative ways to link to external data.

  • DDE stands for "Dynamic Data Exchange", which is a predecessor of OLE, "Object Linking and Embedding".
  • Cross-platform links, for example from a LibreOffice installation running on a Windows machine to a document residing on a Linux machine, are not supported.
  • The LibreOffice DDE server is called "soffice" and it enables data to be shared between Writer and Calc files. However more generally, the DDE function is not portable because it depends on the availability of external DDE servers and their interpretation of the Topic/File and Item/Range arguments.
  • If you use Microsoft Excel to open a Calc spreadsheet that contains calls to the DDE function, the DDE function calls will be replaced by static data because Excel does not provide an equivalent function.
  • When you open a Calc spreadsheet that contains DDE links, you may be warned that automatic update of external links has been disabled. Click the Allow updating button to enable the links.
  • The command Edit ▸ Links to External Files on the Menu bar lists DDE links, and allows them to be modified, manually updated, or broken. When a DDE link is initially created, the Update setting on the Edit Links dialog defaults to Automatic. This setting means that if you have both files open and you make a change to the linked data, that change will be reflected automatically in the file containing the DDE function call, without any further interaction. For an individual DDE link, you can change the Update setting on the Edit Links dialog to Manual. With this setting, any changes to the linked data will not be reflected in the file containing the DDE function call until you manually update the link.
  • The DDE function is especially powerful when entered as an array formula. To create an array formula, type the formula in the Input line in the upper part of the Calc window and on completion press Shift+Control+Enter (Shift+Command+Enter on macOS) rather than the more usual Enter. Alternatively, use the Function Wizard to construct the call to the DDE function and tick the Array toggle button before pressing OK. If you click on a cell that contains an array formula and view the formula in the Input line, then the content of that cell will be surrounded by curly brackets, for example {=DDE("soffice"; "C:\My Files\league tables.ods"; "Division1")}. It is not possible to create an array formula by simply typing the curly brackets around a non-array formula.
  • An example of using DDE in an array formula might arise when a DDE link is to be created so that a section of text in a Writer document (comprising multiple paragraphs) can be copied into a Calc spreadsheet. You might enter a non-array formula such as =DDE("soffice"; "C:\report.odt"; "Introduction") to create this link and import text from the Writer section named "Introduction" located in the file "C:\report.odt". However, this formula will only provide the first paragraph of the section; to include all paragraphs of the section, enter the DDE link as an array formula. It should be noted that once you have created the DDE link using an array formula, the maximum number of paragraphs is limited to the number returned initially. Additional paragraphs added to the end of the section in the Writer document will not appear in the Calc spreadsheet. Similarly, if you use an array formula to create a DDE link to a table in a Writer file, then the maximum dimensions of the table in the Calc spreadsheet would be limited to its original size.
  • 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 Edit ▸ Paste Special ▸ Paste Special ▸ As Link option. The link is inserted as a DDE function, as an array formula.

Examples:

Copying data from another spreadsheet

Formula Description Returns
=DDE("soffice"; "c:\spreadsheets\dde_test.ods"; "Sheet1.D10") where cell Sheet1.D10 in the specified spreadsheet contains the number 345.678. Here the function returns the contents of cell D10 in Sheet1 of the spreadsheet c:\spreadsheets\dde_test.ods. The returned number is displayed in the default cell style. 345.678
=DDE("soffice"; "c:\spreadsheets\dde_test.ods"; "Sheet1.D11"; 2) where cell Sheet1.D11 in the specified spreadsheet contains the number 456.789. Here the function returns the contents of cell D11 in Sheet1 of the spreadsheet c:\spreadsheets\dde_test.ods. The returned value is displayed as text rather than as a number. 456.789
=DDE(A1; A2; A3; A4) where cells A1 to A3 contain the strings "soffice", "c:\spreadsheets\dde_test.ods", and "Sheet1.D12" respectively (typed without quotation marks), while cell A4 contains the number 2; Sheet1.D12 in the specified spreadsheet contains the number 567.89. Here the function returns the contents of cell D12 in Sheet1 of the spreadsheet c:\spreadsheets\dde_test.ods. The returned value is displayed as text rather than as a number. 567.89

Copying data from a text document

In the following example, the Writer file c:\spreadsheets\analysis of speed data.odt includes a table named raw-measurements that contains the following data:

Content of raw-measurements table
time km/h
00:00 66
00:00 61
00:28 38
00:28 82
00:28 82
00:43 46
00:43 104
00:43 114
01:12 101
Formula Description Returns
{=DDE("soffice", "c:\spreadsheets\analysis of speed data.odt", "raw-measurements", 2)} with the formula entered in cell A1. Here the formula has been entered as an array formula via Shift+Control+Enter (Shift+Command+Enter on macOS). The formula returns the contents of the raw-measurements table from the Writer file c:\spreadsheets\analysis of speed data.odt and displays that data in a single column. Each paragraph in a cell of the source table 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 by other rows in order.

With the sample data given above:

  • The text "time" appears in cell A1.
  • The text "km/h" appears in cell A2.
  • The text "00:00" appears in cell A3.
  • The text "66" appears in cell A4.
  • The text "00:00" appears in cell A5.
  • The text "61" appears in cell A6.

and so on.

See Description

For the following examples, assume that there is a Writer file named mottos.odt in the folder c:\miscellaneous. This document contains a section named Various mottos which contains three paragraphs, comprising the following text strings:

  • Think before you speak.
  • Tomorrow is another day.
  • Time you enjoy wasting is not wasted time.

Note that in Writer, sections are created and named using Insert ▸ Section on the Menu bar and the names of existing sections can be viewed using the Navigator (View ▸ Navigator).

Formula Description Returns
=DDE("soffice"; "c:\miscellaneous\mottos.odt"; "Various mottos") Here the function returns the contents of the first paragraph in the section. Think before you speak.
{=DDE("soffice"; "c:\miscellaneous\mottos.odt"; "Various mottos")} with the formula entered in cell A1. Here the formula has been entered as an array formula via Shift+Control+Enter (Shift+Command+Enter on macOS). In this case all three strings are returned and Calc arranges them in a column. Cell A1 shows "Think before you speak.", cell A2 shows "Tomorrow is another day.", and cell A3 shows "Time you enjoy wasting is not wasted time.". A1 - Think before you speak.

A2 - Tomorrow is another day.
A3 - Time you enjoy wasting is not wasted time.

Related LibreOffice functions:

None.

ODF standard:

Section 6.11.2, part 2

Equivalent Excel functions:

None