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.

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

  1. Open the Calc document where the external data is to be inserted. This is the target document.
  2. Select the cell where the upper left-hand cell of the external data is to be inserted.
  3. Choose Insert > Link to External Data.
  4. On the External Data dialog (Figure 1), 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.
  5. 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 n seconds.
  6. Click OK to save and close this dialog.
  7. Figure 1
    Figure 1. Selecting a table or range in a source document from the Web.

Notes

  1. The Available tables/ranges list remains empty until you press Enter after selecting the URL of the source.
  2. The OK button remains unavailable (greyed out) until you select one or more tables/ranges in the list. You can hold down the Control key while clicking on tables/ranges to select more than one.
  3. No images are imported.

Using the Navigator

  1. In Calc, use File > Open from the menu bar to open the document from which the external data is to be taken (source document). You may need to type (or paste) the address (URL) of the web page or other document into the File name box, as shown in Figure 2. For a web page, you can copy the URL from the address bar in your web browser. Choose Web Page Query (OpenOffice.org Calc) as the file type (Figure 2).
  2. Figure 2
    Figure 2. Opening a file using the Web Page Query filter.

  3. Open the OpenOffice.org Calc spreadsheet in which the external data is to be inserted (target document).
  4. In the target document, open the Navigator.
  5. At the bottom of the Navigator, select the source document (the one you opened in step 1). In Figure 2, the source is named actives.
  6. 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.

  7. In the Navigator, select the Insert as Link drag mode, as shown in Figure 3.
  8. 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.
  9. Figure 3
    Figure 3. Selecting a data range in a source document, to be inserted as a link.

  10. 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 (see Figure 4).
  11. Figure 4
    Figure 4. Linked areas in target 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 tell which one to select?

Go to the source document, which you opened in Calc using the Web Page Query filter. Select the range you want. Look in the Navigator: the range name is highlighted (see Figure 5).

If the Formula Bar is visible, the range name is also displayed in the Name box at the left-hand end (see Figure 6).

Figure 5
Figure 5. Using the Navigator to find a data range name.

Figure 6
Figure 6. Using the Name box on the Formula bar to find a data range name.

Share On Facebook
Share On Twitter
Share On Google Plus
Share On Linkedin

Categories

Archives