Chapter 28. Excel and the Internet

IN THIS CHAPTER

Saving Excel files in HTML format

Creating hyperlinks

Importing data from a Web page

Most people who use a computer are connected to the Internet. The Web has become an important way to share and gather information from myriad sources. To help you with these tasks, Excel has the capability to create files that you can use on the Internet and also to gather and process data from the Web. This chapter covers topics related to Excel and the Internet.

28.1. Understanding How Excel Uses HTML

HTML (HyperText Markup Language) is the language of the World Wide Web. When you browse the Web, most documents that your browser retrieves and displays are in HTML format. An HTML file consists of text information plus special tags that describe how the text is to be formatted. The browser interprets the tags, applies the formatting, and displays the information.

28.2. Understanding the Different Web Formats

You can save an Excel workbook so that it's viewable in a Web browser. When you save an Excel workbook for viewing on the Web, you have two options:

• An HTML file: Produces a static Web page, plus a folder that contains support files. You can create the HTML file from the entire workbook or from a specific sheet.

• A single file Web page: Produces a MIME HTML file (*.mht; *.mhtml). Not all browsers can open these files.

These options are described in the following sections. Both examples use a simple two-sheet workbook file. Each sheet has a table and a chart. Figure 28.1 shows one of these worksheets.

Keep in mind that these files are intended to be only displayed by a Web browser. They are not interactive files. In other words, the user cannot make any changes while viewing the file.

Figure 28.1. This workbook will be saved in Excel Web formats.

28.2.1. Creating an HTML file

To save a workbook as an HTML file:

Although Excel can open the HTML files that it creates, essential information is lost. For example, formulas are lost, and charts appear as static graphic images. Therefore, if you might need to make changes later on, make sure you keep a copy of your work in a standard Excel file format.

Figure 28.2 shows how Sheet1 of the example file looks in a browser: Firefox, in this case. Notice that the workbook's sheet tabs appear along the bottom, and you can switch sheets just like you do in Excel.

In addition to the webpage1.htm file, Excel also created a folder named webpage1_files. This folder contains additional files that must be kept with the main HTML file. Therefore, if you post such a file on a Web server, don't forget to also post the accompanying directory.

28.2.2. Creating a single file Web page

In the previous section, I discuss how creating an HTML file with Excel also creates a folder of additional files. The procedure for creating a Web page that uses a single file is exactly the same, except for Step 3. In Step 3, select Single File Web Page (*.mht; *.mhtml) from the Save as Type drop-down list.

Figure 28.3 shows the example file displayed in Internet Explorer.

As I mention previously, not all browsers can open single-file MHTM files. Two that can are Microsoft Internet Explorer (IE) and Opera. Other browsers (such as Firefox and Google Chrome) may require an add-on to display these files.

Figure 28.2. Viewing the HTML file in a browser.

Figure 28.3. Viewing the single file Web page in Internet Explorer.

28.3. Opening an HTML File

Excel can open most HTML files, which can be stored on your local drive or on a Web server. Choose File Open and locate the HTML file. If the file is on a Web server, you'll need to copy the URL and paste it into the File Name field in the Open dialog box.

How the HTML code renders in Excel varies considerably. Sometimes, the HTML file may look exactly as it does in a browser. Other times, it may bear little resemblance, especially if the HTML file uses Cascading Style Sheets (CSS) for layout.

After opening an HTML file, you can work with the information using the normal Excel tools.

Another way to open an HTML file from a Web server is to use a Web query, which is discussed later in this chapter (see "Using Web Queries").

28.4. Working with Hyperlinks

A hyperlink is clickable text that provides a quick way to jump to other workbooks and files. You can set up hyperlinks to display files on your own computer, your network, and the Web. For example, you can create a series of hyperlinks to serve as a table of contents for a workbook. Or, you can insert a hyperlink that displays a Web page in the default Web browser.

28.4.1. Inserting a hyperlink

You can create hyperlinks from cell text or graphic objects, such as shapes and pictures. To create a text hyperlink in a cell, select the cell and choose Insert Links Hyperlink (or press Ctrl+K). Excel responds with the Insert Hyperlink dialog box, as shown in Figure 28.4.

Figure 28.4. Use the Insert Hyperlink dialog box to add hyperlinks to your Excel worksheets.

Select an icon in the Link To column that represents the type of hyperlink you want to create. You can create hyperlinks to a file on your hard drive, a Web page on the Internet, a new document, or a location in your current workbook. In addition, you can create a hyperlink that consists of an e-mail address. Then, specify the location of the file that you want to link to. The dialog box changes, depending upon the icon selected. If you like, click the ScreenTip button to provide some additional text that appears as a mouse-hover—activated ToolTip. Click OK, and Excel creates the hyperlink in the active cell.

Figure 28.5 shows a worksheet with hyperlinks that function as a table of contents for a workbook. Clicking a link activates a worksheet in the workbook. The example also shows an e-mail address that when clicked, activates the default e-mail program.

The appearance of hyperlinks in cells is controlled by two styles in the Style Gallery. The Hyperlink style controls the appearance of hyperlinks that haven't been clicked, and the Followed Hyperlink style controls the appearance of "visited" hyperlinks. To change the appearance of your hyperlinks, modify either or both of those styles. See Chapter 6 for information about using and modifying document styles.

To add a hyperlink to a Shape, select the Shape and then choose Insert Links Hyperlink (or, press Ctrl+K). Specify the required information in the Insert Hyperlink dialog box, as outlined earlier in this section. (Read more about Shapes in Chapter 22.)

Figure 28.5. Hyperlinks in a workbook.

28.4.2. Using hyperlinks

When you hover your mouse pointer over a cell that contains a hyperlink, the mouse pointer turns into a hand. Click the hyperlink, and you're taken to the hyperlinked document.

To select a cell that has a hyperlink with your mouse (without following the hyperlink), position your mouse over the cell, click, and hold for a second or two before you release the mouse button. Or just activate a nearby cell and use the navigation keys to select the cell that contains the hyperlink.

When you hover your pointer over a Shape that contains a hyperlink, the mouse pointer turns into a hand. To follow a hyperlink from a Shape, just point to the Shape and click.

If the hyperlink contains an e-mail address, your default e-mail program launches so that you can send an e-mail to the address specified when you created the hyperlink.

28.5. Using Web Queries

Excel enables you to pull in data contained in an HTML file by performing a Web query. The data is transferred to a worksheet, where you can manipulate it any way you like. Web queries are especially useful for data that is frequently updated, such as stock market quotes.

The term "Web Query" is a bit misleading. You can perform a Web Query on a local HTML file, a file stored on a network server, or a file stored on a Web server on the Internet. To retrieve information from a Web server, you must be connected to the Internet. After the information is retrieved, an Internet connection is not required to work with the information (unless you need to refresh the query).

The best part about a Web query is that Excel remembers where the data came from. Therefore, after you create a Web query, you can refresh the query to pull in the most recent data.

To create a Web query

After you create your Web query, you have some options. Right-click any cell in the data range and choose Data Range Properties from the shortcut menu. Excel displays the External Data Range Properties dialog box, shown in Figure 28.7. These settings control when the data is refreshed, how it is formatted, and what happens if the amount of data changes when the query is refreshed.

To force a refresh at any time, right-click any cell in the data range and choose Refresh. The data in your worksheet is replaced by the latest of content of the Web page.

Figure 28.6. Use the New Web Query dialog box to specify the source of the data.

Figure 28.7. Use the External Data Range Properties dialog box to specify how Excel handles the imported data.

28.6. Other Internet-Related Features

Excel offers a few other Internet-related features, which I briefly describe here. Access these features from the Share tab of Backstage View (choose File Share). Figure 28.8 shows the Share options:

• Send Using E-Mail: Enables you to send the active workbook to one or more recipients via e-mail. The file can be the actual workbook, a PDF file, or an XPS file. If the workbook is saved to a shared location, you can send a link to the file (rather than the actual file). An additional option lets you fax the workbook (assuming that your system has a fax service provider).

• Save to SkyDrive: If you have a SkyDrive account, you can save the workbook to a folder on the Internet, where it can be accessed by others. SkyDrive is a free service offered by Microsoft (part of Windows Live). Go to www.windowslive.com for details.

• Save to SharePoint: Publish the workbook to your SharePoint server. A SharePoint server is a central location where files can be shared and worked on in collaborative manner. Check with your system administrator to see whether you have access to a SharePoint server.

• Publish to Excel Services: Excel Services also requires a SharePoint server.

Figure 28.8. The Share tab of Backstage View.