Embedding spreadsheets

From Apache OpenOffice Wiki
< Documentation‎ | OOo3 User Guides‎ | Calc Guide
Revision as of 20:40, 6 July 2018 by Sancho (Talk | contribs)

(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to: navigation, search

Spreadsheets can be embedded in other OOo files. This is often used in Writer or Impress documents so that Calc data can be used in a text document. You can embed the spreadsheet as either an OLE or DDE object. The difference between a DDE object and a Linked OLE object is that a Linked OLE object can be edited from the document in which it is added as a link, but a DDE object cannot.

For example, if a Calc spreadsheet is pasted into a Writer document as a DDE object, then the spreadsheet cannot be edited in the Writer document. But if the original Calc spreadsheet is updated, the changes are automatically made in the Writer document. If the spreadsheet is inserted as a Linked OLE object into the Writer document, then the spreadsheet can be edited in the Writer as well as in the Calc document and both documents are in sync with each other.

Object Linking and Embedding (OLE)

The major benefit of an OLE (Object Linking and Embedding) object is that it is quick and easy to edit the contents just by double-clicking on it. You can also insert a link to the object that will appear as an icon rather than an area showing the contents itself.

OLE objects can be linked to a target document or be embedded in the target document. Linking inserts information which will be updated with any subsequent changes to the original file, while embedding inserts a static copy of the data. If you want to edit the embedded spreadsheet, double-click on the object.

To embed a spreadsheet as an OLE object in a presentation:

  1. Place the cursor in the document and location you want the OLE object to be.
  2. Select Insert > Object > OLE Object. The dialog shown below opens.
  3. You can either create a new OLE object or create from a file.
Insert OLE object dialog

To create a new object:

  1. Select Create new and select the object type among the available options.
  2. Documentation note.png “Further objects” is only available under a Windows operating system.
  3. Click OK. An empty container is placed in the slide.
  4. Double-click on the OLE object to enter the edit mode of the object. The application devoted to handling that type of file will open the object.
  5. Documentation note.png If the object inserted is handled by OpenOffice.org, then the transition to the program to manipulate the object will be seamless; in other cases the object opens in a new window and an option in the File menu becomes available to update the object you inserted.

To insert an existing object:

  1. To create from a file, select Create from file. The dialog box changes to look like the example below.
  2. To insert the object as a link, select the Link to file option. Otherwise, the object will be embedded.
  3. Click Search, select the required file in the file picker window, then click Open. A section of the inserted file is shown in the document.
Inserting an object as a link

Other OLE objects

Under Windows, the Insert OLE Object dialog box has an extra entry, Further objects.

  1. Double-click on the entry Further objects to open the dialog shown below.
  2. Advanced dialog to insert an OLE object under Windows
  3. Select Create New to insert a new object of the type selected in the Object Type list, or select Create from File to create a new object from a file.
  4. If you choose Create from File, the dialog shown below opens. Click Browse and choose the file to insert. The inserted file object is editable by the Windows program that created it.
  5. Insert object from a file

If instead of inserting an object, you want to insert a link to an object, select the Display As Icon option.

Non-linked OLE object

If the OLE object is not linked, it can be edited in the new document. For instance, if you insert a spreadsheet into a Writer document, you can essentially treat it as a Writer table (with a little more power). To edit it, double-click on it.

Linked OLE object

When the spreadsheet OLE object is linked, if you change it in Writer it will change in Calc; if you change it in Calc, it will change in Writer. This can be a very powerful tool if you create reports in Writer using Calc data, and want to make a quick change without opening Calc.

Documentation note.png You can only edit one copy of a spreadsheet at a time. If you have a linked OLE spreadsheet object in an open Writer document and then open the same spreadsheet in Calc, the Calc spreadsheet will be a read-only copy.

Dynamic Data Exchange (DDE)

DDE is an acronym for Dynamic Data Exchange, a mechanism whereby selected data in document A can be pasted into document B as a linked, ‘live’ copy of the original. It would be used, for example, in a report written in Writer containing time‑varying data, such as sales results sourced from a Calc spreadsheet. The DDE link ensures that, as the source spreadsheet is updated so is the report, thus reducing the scope for error and reducing the work involved in keeping the Writer document up to date.

DDE is a predecessor of OLE. With DDE, objects are linked through file reference, but not embedded. You can create DDE links either within Calc cells in a Calc sheet, or in Calc cells in another OOo doc such as in Writer.

DDE link in Calc

Creating a DDE link in Calc is similar to creating a cell reference. The process is a little different, but the result is the same.

  1. In Calc, select the cells that you want to make the DDE link to.
  2. Copy them: Edit > Copy or Ctrl+C.
  3. Go to the place in the spreadsheet where you want to the link to be.
  4. Select Edit > Paste Special.
  5. When the Paste Special dialog opens, select the Link option on the bottom left of the dialog. Click OK.

The cells now reference the copied data, and the formula bar shows a reference beginning with {=DDE.

If you now edit the original cells, the linked cells will update.

Paste Location of Link option on Paste Special dialog in Calc

DDE link in Writer

The process for creating a DDE link from Calc to Writer is similar to creating a link within Calc.

  1. In Calc, select the cells to make the DDE link to. Copy them.
  2. Go to the place in your Writer document where you want the DDE link. Select Edit > Paste Special.
  3. Select DDE Link. Click OK.

Now the link has been created in Writer. When the Calc spreadsheet is updated, the table in Writer is automatically updated.

Paste Special dialog in Writer, with DDE link selected

Content on this page is licensed under the Creative Common Attribution 3.0 license (CC-BY).
Personal tools