Chapter 29: Excel and the Internet

In This Chapter

Saving a workbook to an internet location

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, collaborate, 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.

Saving a Workbook on the Internet

Excel 2013 makes it very easy to save your work to your SkyDrive or to a SharePoint site. Doing so lets you access the workbook from any computer that has Internet access, no matter where you are. And the computer doesn't have to have Excel installed. Sign in to your SkyDrive or SharePoint account, and you can download the file and work on it locally or view it (and perhaps do minor editing) directly in your web browser using the Excel Web App.

To save a file to the Internet, choose File ⇒ Save As, and then select SkyDrive or SharePoint in the Places section of the Save As screen. Click Browse, and choose a directory for the file. If you want, you can share the workbook with others — but only one person at a time can edit the file. You must be signed in to your SkyDrive or SharePoint account in order to save a file to one of these locations.

Figure 29.1 shows an Excel workbook that was saved to a SkyDrive account. It's displayed in a browser using the Excel Web App. The file is a two-sheet workbook, with sheet tabs at the bottom. As you can see, the Excel Web App includes a modified Ribbon, and it works much like the standard desktop version of Excel.

Figure 29.1

A workbook displayed in a browser using the Excel Web App.

Compared to the desktop version, the Excel Web App has quite a few limitations. For example, formatting options are limited, it can't run VBA macros, it can't create pivot tables — but you can view and manipulate them. There's also a size limitation. If your file is too large, you can't open it with the Excel Web App. But overall, it's very impressive software.

Saving Workbooks in HTML Format

Hypertext Markup Language (HTML) 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.

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 29.2 shows one of these worksheets.

Figure 29.2

This workbook will be saved in Excel web formats.

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 can't make any changes while viewing the file.

Creating an HTML file

To save a workbook as an HTML file:

1. (Optional but recommended) Save the workbook as a normal Excel file.

2. Choose File ⇒ Save As. The Save As dialog box appears.

3. Select Web Page (*.htm; *.html) from the Save as Type drop-down list.

4. Specify what to save (either entire workbook or the active sheet).

5. Specify a filename and then choose a location for the file.

6. Click Save to create the HTML file. Excel may display a message warning you that some features in the workbook are not compatible with the web page format. You can ignore this message.

Figure 29.3 shows how Sheet1 of the example file looks in a browser — Google Chrome, in this case. Notice that the workbook's sheet tabs appear along the bottom, and you can switch sheets just as you do in Excel.

Figure 29.3

Viewing the HTML file in a browser.

In addition to the webpage.htm file, Excel also created a folder named webpage_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.

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 29.4 shows the example file displayed in Internet Explorer.

Figure 29.4

Viewing the single-file web page in Internet Explorer.

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. Files opened directly from a web server are opened in read-only mode. 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”).

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 stored 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 your default web browser.

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). The Insert Hyperlink dialog box, shown in Figure 29.5, appears.

Figure 29.5

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 29.6 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.

Figure 29.6

Hyperlinks in a workbook.

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.

 You can read more about Shapes in Chapter 23.

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.

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.

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 because this operation is not limited to the web. 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, follow these steps:

1. Choose Data ⇒ Get External Data ⇒ From Web. The New Web Query dialog box appears. This dialog box is resizable and functions as a web browser.

2. Type the URL of the HTML file in the Address field. The HTML file can be on the Internet, on a corporate intranet, or on a local or network drive. The file is displayed in the New Web Query dialog box, and each table in the document is indicated by an arrow in a yellow box (see Figure 29.7).

Figure 29.7

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

3. Click one or more arrows to select the table(s) you want to import.

4. You can also control how the imported data is formatted. In the New Web Query dialog box, click Options (upper right). The Web Query Options dialog box appears. Select the desired formatting, and then click OK to return to the New Web Query dialog box.

5. When you're ready to retrieve the information, click Import. The Import Data dialog box appears.

6. Choose where you want to place the data. The information on the web page is retrieved and placed on your worksheet.

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

Figure 29.8

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

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.

E-Mail Features

Excel makes it easy to e-mail your work to others. You can access the e-mail features from the Share tab of Backstage view (choose File ⇒ Share).

You can 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).