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).
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 the External 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 be able to easily determine which is which; in that case, the Navigator method may be easier.
Using the External Data dialog
- Open the Calc document where the external data is to be inserted. This is the target document.
- Select the cell where the upper left-hand cell of the external data is to be inserted.
- Choose Insert > Link to External Data.
- On the External Data dialog, type the URL of the source document or click the [...] button to open a file selection dialog. Press Enter to get Calc to load the list of available tables.
- In the Available tables/range list, select the named ranges or tables you want to insert. You can also specify that the ranges or tables are updated every (number of) seconds.
- Click OK to close this dialog and insert the linked data.
 |
The Available tables/ranges list remains empty until you press Enter after selecting the URL of the source. If you select the source document using the [...] button, then pressing Enter is not required. |
|
The OK button remains unavailable (grayed out) until you select one or more tables/ranges in the list. You can hold down the Ctrl key while clicking on tables/ranges to select more than one. |
|
No images are imported. |
- Open the OpenOffice.org Calc spreadsheet in which the external data is to be inserted (target document).
- 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.
- In the target document, press F5 to open the Navigator.
- At the bottom of the Navigator, select the source document. (In the example below, 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.
- In the Navigator, select the Insert as Link drag mode, as shown below.
- 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.
- In the target document, check the Navigator. Instead of a + by Range names, it shows a + by Linked areas. Click the + to see the same range name.
How to find the required data range or table
The examples above show that the import filter gave names to the data ranges (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 tell which one to select?
Go to the source document, which you opened in Calc. In the Navigator, double-click on the range name: that range is highlighted on the sheet as shown below.
If the Formula Bar is visible, the range name is also displayed in the Name box at the left-hand end.
| Content on this page is licensed under the Creative Common Attribution 3.0 license (CC-BY). |
