Calc Guide

10

Chapter
Linking Calc Data
Sharing data in and out of Calc
This PDF is designed to be read onscreen, two pages at atime. If you want to print a copy, your PDF viewer shouldhave an option for printing two pages on one sheet ofpaper, but you may need to start with page 2 to get it toprint facing pages correctly. (Print this cover page
separately.)CopyrightThis document is Copyright © 2006–2010 by its contributors as listed
in the section titled Authors. You may distribute it and/or modify it
under the terms of either the GNU General Public License, version 3 orlater, or the Creative Commons Attribution License, version 3.0 or
later.
All trademarks within this guide belong to their legitimate owners.

Authors
John KanePeter Kupfer
Iain Roberts
Rob ScottNikita Telang
Jean Hollis Weber
James Andrew

Feedback
Please direct any comments or suggestions about this document to:authors@documentation.openoffice.org

Publication date and software version
Published 20 March 2010. Based on OpenOffice.org 3.2.

You can download
an editable version of this document fromhttp://oooauthors.org/english/userguide3/published/

Contents
Copyright............................................................................................... 2
Why use multiple sheets?.......................................................................5
Setting up multiple sheets.....................................................................5
Identifying sheets...............................................................................5
Inserting new sheets...........................................................................6
Renaming sheets................................................................................. 8
Referencing other sheets.......................................................................9
Creating the reference with the mouse..............................................9
Creating the reference with the keyboard........................................11
Referencing other documents..............................................................11
Creating the reference with the mouse............................................11
Creating the reference with the keyboard........................................12
Hyperlinks and URLs...........................................................................13
Relative and absolute hyperlinks......................................................13
Creating hyperlinks..........................................................................14
Editing hyperlinks............................................................................. 16
Removing hyperlinks........................................................................16
Linking to external data.......................................................................17
Using the External Data dialog.........................................................17
Using the Navigator.......................................................................... 18
How to find the required data range or table...................................20
Linking to registered data sources.......................................................22
Viewing data sources........................................................................24
Editing data sources.........................................................................24
Launching Base to work on data sources..........................................25
Using data sources in Calc spreadsheets..........................................25
Embedding spreadsheets.....................................................................26
Object Linking and Embedding (OLE)..............................................26
Other OLE objects.........................................................................28
Non-linked OLE object...................................................................29
Linked OLE object.........................................................................29
Linking Calc Data

3

Dynamic Data Exchange (DDE)........................................................30
DDE link in Calc............................................................................. 30
DDE link in Writer.........................................................................31

4

Linking Calc Data

Why use multiple sheets?
Chapter 1 introduced the concept of multiple sheets in a spreadsheet.
Multiple sheets help keep information organized; once you link those
sheets together, you unleash the full power of Calc. Consider this case.
John is having trouble keeping track of his personal
finances. He has several bank accounts and the informationis scattered and disorganized. He can’t get a good grasp on
his finances until he can see everything at once.
To resolve this, John decided to track his finances in OOo
Calc. John knows Calc can do simple mathematicalcomputations to help him keep a running tab of his
accounts, and he wants to set up a summary sheet so that
he can see all of his account balances at once.
This can be accomplished easily.

Note

For users with experience using Microsoft Excel, a Calc sheet iscalled either a sheet or worksheet in Excel. What Excel calls aworkbook, Calc calls a spreadsheet (the whole document).

Setting up multiple sheets
Chapter 1 gives a detailed explanation of how to set up multiple sheets
in a spreadsheet. Here is a quick review.

Identifying sheets
When you open a new spreadsheet it has, by default, three sheets
named Sheet1, Sheet2, and Sheet3. Sheets in Calc are managed usingtabs at the bottom of the spreadsheet, as shown in Figure 1.

Figure 1: Default sheet tabs

Setting up multiple sheets5

Inserting new sheets
There are several ways to insert a new sheet. The first step, in allcases, is to select the sheet that will be next to the new sheet. Then do
any of the following:• Select Insert > Sheet from the menu bar, or• Right-click on the tab and select Insert Sheet, or• Click in an empty space at the end of the line of sheet tabs (seeFigure 2).
Click here tocreate a new
sheet

Figure 2: Creating a new sheet
Each method opens the Insert Sheet dialog (Figure 3). Here you canchoose to put the new sheet before or after the selected sheet and how
many sheets to insert.

Figure 3. Insert Sheet dialog

6

Linking Calc Data

We need 6 sheets, one for each of the 5 accounts and one as asummary sheet so we will add 3 more. We also want to name each ofthese sheets for the account they represent: Summary, Checking
Account, Savings Account, Credit Card 1, Credit Card 2, and Car Loan.
We have two choices: insert 3 new sheets and rename all 6 sheets
afterwards; or rename the existing sheets, then insert the 3 new
sheets one at a time, renaming each new sheet during the insert step.
To insert sheets and rename afterwards:
1) In the Insert Sheet dialog, choose the position for the new sheets(in this example, we use After current sheet).
2) Choose New sheet and 3 as the No. of sheets. (Three sheets are
already provided by default.) Because you are inserting more than
one sheet, the Name box is not available.
3) Click OK to insert the sheets.
4) For the next steps, go to “Renaming sheets” below.
To insert sheets and name them at the same time:1) Rename the existing sheets Summary, Checking Account, andSavings Account, as described in “Renaming sheets” below.
2) In the Insert Sheet dialog, choose the position for the first new
sheet.
3) Choose New sheet and 1 as the No. of sheets. The Name box is
now available.
4) In the Name box, type a name for this new sheet, for example
Credit Card 1.
5) Click OK to insert the sheet.
6) Repeat steps 1–4 for each new sheet, giving them the names
Credit Card 2 and Car Loan.
On the Insert Sheet dialog, you can also add a sheet from a different
spreadsheet file (for example, another Calc or Excel spreadsheet), bychoosing the From file option. Click Browse and select the file; a listof the available sheets appears in the box. Select the sheet to import.
If, after you select the file, no sheets appear you probably selected aninvalid file type (not a spreadsheet, for example).
Tip

For a shortcut to inserting a sheet from another file, choose
Insert > Sheet from file from the menu bar. The Insert Sheetdialog opens with the From file option preselected, and then theInsert dialog opens on top of it.

Setting up multiple sheets7

If you prefer, select the Link option to insert the external sheet as alink instead as a copy. This is one of several ways to include “live” datafrom another spreadsheet. (See also “Linking to external data” on page17.) The links can be updated manually to show the current contents ofthe external file; or, depending on the options you have selected inTools > Options > OpenOffice.org Calc > General > Updating,whenever the file is opened.

Renaming sheets
Sheets can be renamed at any time. To give a sheet a more meaningful
name:• Enter the name in the name box when you create the sheet, or• Double click on the sheet tab, or• Right-click on a sheet tab, select Rename Sheet from the pop-up

menu and replace the existing name.
Note

If you want to save the spreadsheet to Microsoft Excel format,
the following characters are not allowed in sheet names: \ / ? * [ ]: and ' as the first or last character of the name.

Your sheet tab area should now look like this.

Figure 4: Six renamed sheets
Now we will set up the account ledgers. This is just a simple summarythat includes the previous balance plus the amount of the current
transaction. For withdrawals, we enter the current transaction as anegative number so the balance gets smaller. A basic ledger is shownin Figure 5.
This ledger is set up in the sheet named Checking Account. The totalbalance is summed in cell F3. You can see the equation for it in theformula bar. It is the summary of the opening balance, cell C3, and allof the subsequent transactions.

8

Linking Calc Data

Figure 5: Checking ledger

Referencing other sheets
On the Summary sheet we display the balance from each of the othersheets. If you copy the example in Figure 5 onto each account, thecurrent balances will be in cell F3 of each sheet.
There are two ways to reference cells in other sheets: by entering theformula directly using the keyboard or by using the mouse. We will
look at the mouse method first.

Creating the reference with the mouse
On the Summary sheet, set up a place for all five account balances, so
we know where to put the cell reference. Figure 6 shows a summary
sheet with a blank Balance column. We want to place the reference forthe checking account balance in cell B3.

Figure 6: Blank summary
To make the cell reference in cell B3, select the cell and follow thesesteps.
1) Click on the = icon next to the input line. The icons change andan equals sign appears in the input line as in Figure 7.
Referencing other sheets9

Figure 7: Equal sign in input line
2) Now, click on the sheet tab for the sheet containing the cell to bereferenced. In this case, that is the Checking Account sheet asshown in Figure 8.

Figure 8: Click on the checking account tab3) Click on cell F3 (where the balance is) in the Checking Accountsheet. The phrase ‘Checking Account’.F3 should appear in the
input line as in Figure 9.

Figure 9: Cell reference selected
4) Click the green checkmark in the input line to finish.
5) The Summary sheet should now look like Figure 10.

Figure 10: Finished checking account reference

10

Linking Calc Data

Creating the reference with the keyboard
From Figure 10, you can deduce how the cell reference is constructed.
The reference has two parts: the sheet name (‘Checking Account’) andthe cell reference (F3). Notice that they are separated by a period.
Note

The sheet name is in single quotes because it contains a space,
and the mandatory period (.) always falls outside any quotes.

So, you can fill in the Savings Account cell reference by just typing it
in. Assuming that the balance is in the same cell (F3) in the Savings
Account sheet, the cell reference should be =’Savings Account’.F3 (seeFigure 11).

Figure 11: Savings account reference

Referencing other documents
John decides to keep his family account information in a different
spreadsheet file from his own summary. Fortunately Calc can linkdifferent files together. The process is the same as described for
different sheets in a single spreadsheet, but we add one more step toindicate which file the sheet is in.

Creating the reference with the mouse
To create the reference with the mouse, both spreadsheets need to be
open. Select the cell in which the formula is going to be entered.
1) Click the = icon next to the input line.

Referencing other documents

112) Switch to the other spreadsheet (the process to do this will varydepending on which operating system you are using).
3) Select the sheet (Savings Account) and then the reference cell
(F3).

Figure 12: Selecting the savings account reference cell
4) Switch back to the original spreadsheet.
5) Click on the green check mark on the input line.
Your spreadsheet should now resemble Figure 13.

Figure 13: Linked files
You will get a good feel for the format of the reference if you lookclosely at the input line. Based on this line you can create thereference using the keyboard.

Creating the reference with the keyboard
Typing the reference is simple once you know the format the reference
takes. The reference has three parts to it:• Path and file name• Sheet name
• CellLooking at Error: Reference source not found you can see the thegeneral format for the reference is=’file:///Path & File Name’#$SheetName.CellName.
12

Linking Calc Data

Note

The reference for a file has three forward slashes /// and thereference for a hyperlink has two forward slashes //.

Hyperlinks and URLs
Hyperlinks can be used in Calc to jump to a different location fromwithin a spreadsheet and can lead to other parts of the current file, todifferent files or even to web sites.

Relative and absolute hyperlinks
Hyperlinks can be stored within your file as either relative or absolute.
A relative hyperlink says, “Here is how to get there starting from
where you are now” (meaning from the folder in which your current
document is saved) while an absolute hyperlink says, “Here is how to
get there no matter where you start from”.
An absolute link will stop working only if the target is moved. A
relative link will stop working only if the start and target locations
change relative to each other. For instance, if you have two
spreadsheets in the same folder linked to each other and you move theentire folder to a new location, a relative hyperlink will not break.
To change the way that OOo stores the hyperlinks in your file, selectTools > Options > Load/Save > General and choose if you want
URLs saved relatively when referencing the File System, or theInternet, or both.
Calc will always display an absolute hyperlink. Don’t be alarmed when
it does this even when you have saved a relative hyperlink—this‘absolute’ target address will be updated if you move the file.

Note

Make sure that the folder structure on your computer is thesame as the file structure on your web server if you save yourlinks as relative to the file system and you are going to upload
pages to the Internet.

Tip

When you rest the mouse pointer on a hyperlink, a help tip
displays the absolute reference, since OOo uses absolute path
names internally. The complete path and address can only beseen when you view the result of the HTML export (saving the
spreadsheet as an HTML file), by loading the HTML file as Text,
or by opening it with a text editor.

Hyperlinks and URLs

13

Creating hyperlinks
When you type text that can be used as a hyperlink (such as a website
address or URL), Calc formats it automatically, creating the hyperlink
and applying to the text a color and background shading. If this does
not happen, you can enable this feature using Tools > AutoCorrect
Options > Options and selecting URL Recognition.

Tips

To change the color of hyperlinks, go to Tools > Options >OpenOffice.org > Appearance, scroll to Unvisited links and/orVisited links, pick the new colors and click OK. Caution: thiswill change the color for all hyperlinks in all components of
OpenOffice.org—this may not be what you want.

You can also insert and modify links using the Hyperlink dialog. To
display the dialog, click the Hyperlink iconon the Standard
toolbar or choose Insert > Hyperlink from the menu bar. To turn
existing text into a link, highlight it before opening the dialog.

Figure 14. Hyperlink dialog showing details for Internet links
On the left side, select one of the four categories of hyperlinks:• Internet: the hyperlink points to a web address, normally
starting with http://• Mail & News: the hyperlink opens an email message that the
pre-addressed to a particular recipient• Document: the hyperlink points to a place in either the current
document or another existing document• New document: the hyperlink creates a new document.
14

Linking Calc Data

The top right part of the dialog changes according to the choice madefor the hyperlink category from the left panel. A full description of all
the choices, and their interactions, is beyond the scope of this chapter.
Here is a summary of the most common choices used in spreadsheets.
For an Internet hyperlink, choose the type of hyperlink (Web, FTP, orTelnet), and enter the required web address (URL).
For a Mail and News hyperlink, specify whether it is a mail or news
link, the receiver address and for email, also the subject.
For a Document hyperlink, specify the document path (the Open Filebutton opens a file browser); leave this blank if you want to link to atarget in the same spreadsheet. Optionally specify the target in the
document (for example a specific sheet). Click on the Target in
document icon to open the Navigator where you can select the target,
or if you know the name of the target, you can type it into the box.
For a New Document hyperlink, specify whether to edit the newly
created document immediately (Edit now) or just create it (Editlater), and enter the file name and the type of document to create(text, spreadsheet, etc.). The Select path button opens a directory
picker dialog.
The Further settings section in the bottom right of the dialog is
common to all the hyperlink categories, although some choices are
more relevant to some types of links.
• Set the value of Frame to determine how the hyperlink will open.

This applies to documents that open in a Web browser.
• Form specifies if the link is to be presented as text or as a button.
Figure 15 shows a link formatted as a button.

Figure 15: OOo Authors hyperlink as button• Text specifies the text that will be visible to the user. If you do notenter anything here, Calc will use the full URL or path as the linktext. Note that if the link is relative and you move the file, thistext will not change, though the target will.
Hyperlinks and URLs

15

• Name is applicable to HTML documents. It specifies text that will

be added as a NAME attribute in the HTML code behind thehyperlink.
• Events button: this button will be activated to allow Calc to react
to events for which the user has written some code (macro). Thisfunction is not covered in this chapter.

Note

A hyperlink button is a type of form control. As with all form
controls, it can be anchored or positioned by right-clicking on the
button in design mode. More information about forms can befound in Chapter 15 of the Writer Guide.
For the button to work , the spreadsheet must not be in designmode. To toggle design mode on and off, view the Form Controls
toolbar (View > Toolbars > Form Controls) and click the
Design Mode On/Off button.

Editing hyperlinks
To edit an existing link, place the cursor anywhere in the link and click
the Hyperlink iconon the Standard toolbar or select Insert >Hyperlink from the menu bar. The Hyperlink dialog (Figure 14) opens.
If the Hyperlink is in button form, the spreadsheet must have Design
Mode on in order to edit the Hyperlink. Make your changes and click
Apply. If you need to edit several hyperlinks, you can leave theHyperlink dialog open until you have edited all of them. Be sure toclick Apply after each one. When you are finished, click Close.

Removing hyperlinks
You can remove the clickable link from hyperlink text—leaving just the
text—by right-clicking on the link and selecting Default Formatting.
This option is also available from the Format menu. You may thenneed to re-apply some formatting in order to match the rest of yourdocument.
To erase the link text or button from the document completely, select it
and press the backspace key.

16

Linking Calc Data

Linking to external data
You can insert tables from HTML documents, and data located within
named ranges from an OpenOffice.org Calc or Microsoft Excel
spreadsheet, into a Calc spreadsheet. (To use other data sources,
including database files in OOo Base, see “Linking to registered data
sources” on page 22.)
You can do this in two ways: using the External Data dialog or using
the Navigator. If your file has named ranges or named tables, and you
know the name of the range or table you want to link to, using theExternal Data dialog method is quick and easy. However, if the file has
several tables, and you want to pick only one of them, you may not beable to easily determine which is which; in that case, the Navigatormethod may be easier.

Using the External Data dialog
1) Open the Calc document where the external data is to beinserted. This is the target document.
2) Select the cell where the upper left-hand cell of the external datais to be inserted.
3) Choose Insert > Link to External Data.
4) On the External Data dialog (Figure 16), type the URL of thesource document or click the [...] button to open a file selection
dialog. Press Enter to get Calc to load the list of available tables.
5) In the Available tables/range list, select the named ranges ortables you want to insert. You can also specify that the ranges ortables are updated every n seconds.
6) Click OK to close the dialog and insert the linked data.

Notes1) The Available tables/ranges list remains empty until youpress Enter after typing the URL of the source. If you selectthe source document using the […] button, then pressing
Enter is not required.
2) The OK button remains unavailable (greyed out) until you
select one or more tables/ranges in the list. You can holddown the Ctrl key while clicking on tables/ranges to selectmore than one.
3) No images are imported

Linking to external data

17

Figure 16: Selecting a table or range in a source documentfrom the Web

Using the Navigator1) Open the OpenOffice.org Calc spreadsheet in which the external
data is to be inserted (target document).
2) Open the document from which the external data is to be taken(source document). If the source document is a Web page, choose
Web Page Query (OpenOffice.org Calc) as the file type.

Figure 17: Opening a file using the Web Page Query filter

18

Linking Calc Data

3) In the target document, press F5 to open the Navigator.
4) At the bottom of the Navigator, select the source document. (InFigure 18, the source is named actives.)
The Navigator now shows the range names or the tables
contained in the source document (the example contains range
names; other documents have a list of tables). Click on the + next
to Range names to display the list.

Figure 18: Selecting a data range in asource document, to be inserted as a link5) In the Navigator, select the Insert as Link drag mode, as shownin Figure 18.
6) Select the required range or table and drag it from the Navigator
into the target document, to the cell where you want the upper
left-hand cell of the data range to be.
7) In the target document, check the Navigator. Instead of a + by
Range names, it shows a + by Linked areas. Click the + to see thesame range name (see Figure 19).

Linking to external data

19

Figure 19: Linked areas intarget spreadsheet

How to find the required data range or table
The examples above show that the import filter gave names to the data
rangers (tables) in the sample web page starting from HTML_1. It also
created two additional range names (not visible in the illustration):
HTML_all – designates the entire document
HTML_tables – designates all HTML tables in the document
If the data tables in the source HTML document have been given
names (using the ID attribute on the TABLE tag), or the external
spreadsheet includes named ranges, those names appear in the list
along with the ranges Calc has sequentially numbered.
If the data range or table you want is not named, how can you tellwhich one to select?
Go to the source document, which you opened in Calc. In the
Navigator, double-click on a range name: that range is highlighted on
the sheet (see Figure 20).
If the Formula Bar is visible, the range name is also displayed in theName box at the left-hand end (see Figure 21).

20

Linking Calc Data

Figure 20: Using the Navigator to find a data range nameFigure 21: Using the Name box to find a data range name

Linking to external data

21

Linking to registered data sources
You can access a variety of databases and other data sources and linkthem into Calc documents.
First you need to register the data source with OpenOffice.org. (Toregister means to tell OOo what type of data source it is and where thefile is located.) The way to do this depends on whether or not the data
source is a database in *.odb format.
To register a data source that is in *.odb format:
1) Choose Tools > Options > OpenOffice.org Base > Databases.
2) Click the New button (below the list of registered databases) toopen the Create Database Link dialog(Figure 22).

Figure 22: Registering databases3) Enter the location of the database file, or click Browse to open afile browser and select the database file.
4) Type a name to use as the registered name for the database andclick OK. The database is added to the list of registereddatabases. The OK button is enabled only when both fields arefilled in.
To register a data source that is not in *.odb format:
1) Choose File > New > Database to open the Database Wizard.
2) Select Connect to an existing database. The choices fordatabase type depend on your operating system. For example,

22

Linking Calc Data

Microsoft Access and other Microsoft products are not among the
choices if you are using Linux. In our example, we chose dBASE.

Figure 23: Registering a database using the Database Wizard
3) Click Next. Type the path to the database file or click Browse
and use the Open dialog to navigate to and select the databasefile before clicking Open.
4) Click Next. Select Yes, register the database for me, but clear the
checkbox marked Open the database for editing.
5) Click Finish. Name and save the database in the location of yourchoice. Note: changes made to the *.odb do not affect the originaldBASE file.
Once a data source has been registered, it can be used by any OOo
component (for example Calc).

Linking to registered data sources23

Viewing data sources
Open a document in Calc. To view the data sources available, press F4
or select View > Data Sources from the menu bar. The Data Source
View pane opens above the spreadsheet. A list of registered databases
is in the Data Explorer area on the left. (The built-in Bibliography
database is included in the list.)
To view each database, click on the + to the left of the name of thedatabase. (This has been done for the Automobile database in Figure24.) Click on the + next to Tables to view the individual tables.

Figure 24: Databases
Now click on a table to see all the records held in it. The data records
are displayed on the right side of the Data Source View pane. To see
more columns, you can click the Explorer On/Off button to hide theData Explorer area.
At the top of the Data Source View pane, below the Calc toolbars, isthe Table Data bar. This toolbar includes button for saving records,
editing data, finding records, sorting, filtering, and other functions. For
more details about this toolbar, see the Help for data source browser.
Below the records is the Form Navigation bar, which shows which
record is selected and the total number of records. To the right are fivetiny buttons; the first four move backwards or forwards through therecords, or to the beginning or end.

Figure 25: Data Source View navigation buttons

Editing data sources
Some data sources (such as spreadsheets) cannot be edited in the data
source view.
In editable data sources, records can be edited, added, or deleted. If
you cannot save your edits, you need to open the database in Base andedit it there; see “Launching Base to work on data sources”. You canalso hide columns and make other changes to the display.
24

Linking Calc Data

Launching Base to work on data sources
You can launch OOo Base at any time from the Data Source View pane.
Right-click on a database or the Tables or Queries icons and select
Edit Database File. Once in Base, you can edit, add, and delete tables,
queries, forms, and reports.
For more about using Base, see Chapter 8 (Getting Started with Base)in the Getting Started guide.

Using data sources in Calc spreadsheets
Data from the tables in the data source pane can be placed into Calc
documents in a variety of ways.
You can select a cell or an entire row in the data source pane and dragand drop the data into the spreadsheet. The data is inserted at theplace where you release the mouse button.
An alternative method uses the Data to Text icon and will include thecolumn headings above the data you insert:
1) Click the cell of the spreadsheet which you want to be the top leftof your data including the column names.
2) Press F4 to open the database source window and select the tablecontaining the data you want to use.
3) Select the rows of data you want to add to the spreadsheet:•
Click the gray box to the left of the row you want to select if
only selecting one row. That row is highlighted.
•
To select multiple adjacent rows, hold down the Shift key
while clicking the gray box of the rows you need.
•To select multiple separate rows, hold down the Control key
while selecting the rows. The selected rows are highlighted.
•
To select all the rows, click the gray box in the upper left
corner. All rows are highlighted.
4) Click the Data to text icon
to insert the data into the
spreadsheet cells.
You can also drag the data source column headings (field names) onto
your spreadsheet to create a form for viewing and editing individual
records one at a time. Follow these steps:
1) Click the gray box at the top of the column (containing the fieldname you wish to use) to highlight it.

Linking to registered data sources252) Drag and drop the gray box to where you want the record to
appear in the spreadsheet.
3) Repeat until you have moved all of the fields you need to where
you want them.
4) Close the Data Source window: press F4.
5) Save the spreadsheet and click the Edit File buttonon theStandard toolbar, to make the spreadsheet read-only. All of the
fields will show the value for the data of the first record you
selected.
6) Add the Form Navigation toolbar: View > Toolbars > Form
Navigation. By default, this toolbar opens at the bottom of theCalc window, just above the status bar.
7) Click the arrows on the Form Navigation toolbar to view thedifferent records of the table. The number in the Record box
changes as you move through the records. The data in the fields
changes to correspond to the data for that particular record
number. You can also search for a specific record, sort and filter
records, and do other tasks using this toolbar.

Embedding spreadsheets
Spreadsheets can be embedded in other OOo files. This is often used inWriter 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 isinserted 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 isthat it is quick and easy to edit the contents just by double-clicking on

26

Linking Calc Data

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 thetarget document. Linking inserts information which will be updatedwith 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 below opens.

Figure 26: Insert OLE object dialog
3) You can either create a new OLE object or create from a file.
To create a new object:
1) Select Create new and select the object type among the available
options.
Note“Further objects” is only available if you are using the Windows
operating system.

2) Click OK. An empty container is placed in the slide.
3) Double-click on the OLE object to enter the edit mode of theobject. The application devoted to handling that type of file will
open the object.

Note

If the object inserted is handled by OpenOffice.org, then thetransition to the program to manipulate the object will beseamless; in other cases the object opens in a new window andan option in the File menu becomes available to update theobject you inserted.

Embedding spreadsheets27

To insert an existing object:
1) To create from a file, select Create from file. The dialog changes
to look like Figure 27.
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 Open dialog, thenclick Open. A section of the inserted file is shown in the
document.

Figure 27: Inserting an object as a link

Other OLE objects
Under Windows, the Insert OLE Object dialog box has an extra entry,
Further objects, as shown in Figure 26.
1) Double-click on the entry Further objects to open the dialog
shown in Figure 28.

Figure 28: Inserting an OLE object under Windows

28

Linking Calc Data

2) 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.
3) If you choose Create from File, the dialog shown in Figure 29
opens. Click Browse and choose the file to insert. The inserted
file object is editable by the Windows program that created it.
If instead of inserting an object, you want to insert a link to an
object, select the Display As Icon option.

Figure 29: Insert object from a fileNon-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 itwill 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.
Note

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.

Embedding spreadsheets

29

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 alinked, ‘live’ copy of the original. It would be used, for example, in areport 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 thescope 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 filereference, 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 tobe.
4) Select Edit > Paste Special.
5) When the Paste Special dialog opens, select the Link option on
the bottom left of the dialog (Figure 30). Click OK.
The cells now reference the copied data, and the formula bar shows areference beginning with {=DDE.
If you now edit the original cells, the linked cells will update.

Figure 30: Location of Link option on Paste Special dialog in Calc

30

Linking Calc Data

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 (Figure 31). Click OK.
Now the link has been created in Writer. When the Calc spreadsheet is
updated, the table in Writer is automatically updated.

Figure 31: Paste Special dialog in Writer, with DDE link selected

Embedding spreadsheets

31