PART III Share, Publish, and Present Data
CHAPTER 15 Using Excel’s Web Capabilities
Color profile: Generic CMYK printer profile Composite Default screen
How to…
■ Understand saving directly to an intranet site or Internet server
■ Choose Web options to control how Excel creates web pages
■ Understand HTML, round tripping, and web file formats
■ Save a worksheet or workbook as a web page
■ Work in an interactive web workbook
■ Understand and use Excel’s XML capabilities
In this chapter, you’ll learn how to use Excel’s web capabilities—everything from understanding the considerations involved in saving files directly to an intranet site or Internet server to creating web pages from workbooks and using Excel’s powerful XML capabilities (if they’re included in your version of Excel).
Depending on the type of work you do, only some of this chapter may be relevant to you. For example, while many home businesses and small businesses can benefit directly and immediately from publishing some worksheets on the Web for their customers, typically only larger businesses use XML documents at this point.
Understand Saving Directly to an Intranet Site or Internet Server
Excel can store files directly on a web server, a File Transfer Protocol (FTP) server, or a server running Microsoft’s SharePoint Services. This capability can be very useful for working with intranet sites, because you can open a page on an intranet server directly in Excel, edit or update the page, and then save it. To open a file from a server, you need what’s calledread permission;
to save a file to a server, you needwrite permission.
The technology for opening files from and saving files to web servers is called Web Digital Authoring and Versioning, or WebDAV. Sometimes it’s also called Web Sharing.
If you have a fast and reliable Internet connection, you can work with files on Internet servers (as opposed to intranet sites) as well. Youcanalso work with files on Internet servers across slower or less reliable connections, but the results tend to be less satisfactory. The problem is that if Excel is unable even temporarily to write data to the server, it may be unable to save a file. If the worst comes to the worst, you may lose any unsaved changes in the file.
For this reason, it’s usually best not to work directly with files on Internet servers; even fast and usually reliable Internet connections can suffer glitches severe enough to cost you work.
Instead, use Windows Explorer or another tool to download a copy of any file you need to open in Excel. Then work with the file on your local disk, where you can save changes instantly as often as necessary. When you’ve finished making changes to the file, or when you’ve created a new file that you want to place on the Internet server, upload the file. This way, you keep a copy of the file on your local disk at all times, which will help you avoid losing any data.
15
CHAPTER 15: Using Excel’s Web Capabilities 321
HowTo-Tght (8)/ How to Do Everything with Microsoft Office Excel 2003 / Hart-Davis / 3071-1 / Chapter 15
You can access an intranet server or Internet server via Internet Explorer or another web browser, a third-party graphical FTP client (or even the command-line FTP client built into Windows XP and Windows 2000), or a common dialog box (for example, the Open dialog box or the Save As dialog box). But the most convenient way to access a server is to create a network place for it by using the Add Network Place Wizard. To do so, follow these steps:
1. Choose Start | My Network Places in Windows XP, or double-click the My Network Places shortcut on your Windows 2000 desktop, to display the My Network Places folder.
2. Launch the Add Network Place Wizard:
■ In Windows XP, click the Add a Network Place link in the Network Tasks pane.
■ In Windows 2000, double-click the Add Network Place icon.
3. Follow the steps in the Wizard to create the network place.
Choose Web Options to Control How Excel Creates Web Pages
To control how Excel creates web pages, choose options in its Web Options dialog box.
(The other Office applications have their own Web Options dialog boxes, but the settings you choose in one application don’t affect the other applications.)
To display the Web Options dialog box, follow these steps:
1. Choose Tools | Options to display the Options dialog box.
2. Click the General tab to display it.
3. Click the Web Options button to display the Web Options dialog box.
Choose Options on the General Tab
The General tab of the Web Options dialog box contains two compatibility options:
■ Save Any Additional Hidden Data Necessary to Maintain Formulas check box Controls whether Excel includes in web spreadsheets any hidden data that’s needed to make the formulas work. You’ll almost always want to keep this check box selected, as it is by default.
■ Load Pictures from Web Pages Not Created in Excel check box Controls whether Excel loads pictures in web pages created by other applications. This check box is selected by default.
Choose Options on the Browsers Tab
The Browsers tab of the Web Options dialog box (Figure 15-1) lets you specify the types of
Color profile: Generic CMYK printer profile Composite Default screen
plays safe.) Excel automatically selects and deselects the options in the Options box to match that browser’s needs. You can also select and deselect check boxes manually to suit your needs:
■ Allow PNG As a Graphics Format Select to allow web pages to use the Portable Network Graphics (PNG) format.
■ Rely on CSS for Font Formatting Select to use Cascading Style Sheets (CSS) for font formatting.
■ Rely on VML for Displaying Graphics in Browsers Select to use Vector Markup Language (VML; a text-based format for vector graphics) for displaying graphics.
■ Save New Web Pages As Single File Web Pages Select to make Excel save new web pages using the Single File Web Page format by default. (You can override this setting manually.)
Choose Options on the Files Tab
The Files tab of the Web Options dialog box (Figure 15-2) includes the following check boxes:
■ Organize Supporting Files in a Folder Select to make Excel place the supporting files in a subfolder of the folder that contains the page rather than in the same folder as the page. Using a subfolder tends to be neater and easier, especially when you need to move the page.
■ Use Long File Names Whenever Possible Select to make Excel use long file names if possible when saving files to a web server. You may want to deselect this option to force Excel to use short (eight-character) names.
FIGURE 15-1 On the Browsers tab of the Web Options dialog box, specify the types of browsers for which you want to make the web page work.
15
■ Update Links on Save Select to make Excel automatically update hyperlinks in the page when you save it. Updating the links helps prevent the page containing broken links.
■ Check If Office Is the Default Editor for Web Pages Created in Office Select to make Excel check if it’s the default editor for web pages that Office applications create.
This check box is selected by default but is a matter of preference. If you prefer to use another web editor than Excel, clear this check box to prevent Excel constantly warning you about a choice you know you’ve made.
■ Download Office Web Components Select to make Office download Office Web Components when they’re available. If you select this check box, specify the download location in the Location text box.
Choose Options on the Pictures Tab
On the Pictures tab of the Web Options dialog box, you can select the screen resolution of the monitor on which your web pages will be viewed. The default is 800×600 resolution. You can also specify the number of pixels per inch on that monitor. The default is 96 pixels per inch;
the other available settings are 72 pixels per inch and 120 pixels per inch.
Choose Options on the Encoding Tab
On the Encoding tab of the Web Options dialog box, you can select the type of encoding to use for the web page—for example, Western European (Windows) or Unicode (UTF-8)—and whether to
HowTo-Tght (8)/ How to Do Everything with Microsoft Office Excel 2003 / Hart-Davis / 3071-1 / Chapter 15
CHAPTER 15: Using Excel’s Web Capabilities 323
FIGURE 15-2 On the Files tab of the Web Options dialog box, specify how Excel should handle file names and locations, and whether Excel should check if it’s the default editor for web pages.
Color profile: Generic CMYK printer profile Composite Default screen
Choose Options on the Fonts Tab
On the Fonts tab of the Web Options dialog box (shown here), you can choose the character set, proportional font, and fixed-width font for your web pages. The default character set is English/
Western European/Other Latin Script, and you’ll seldom need to change it unless you need to create, say, Arabic or Japanese pages. On the other hand, you may want to change the fonts for visual effect.
After choosing options, click the OK button to close the Web Options dialog box, and then click the OK button to close the Options dialog box.
Understand HTML, Round Tripping, and Web File Formats
For creating web content, Excel and the other Office applications use Hypertext Markup Language (HTML), a formatting language that’s extensively used and that’s understood more or less perfectly by all modern web browsers. HTML usestags,or codes, to specify how an item should be displayed.
For example, if you apply an <H2> tag to indicate that some text is a level-two heading, any browser should recognize the tag and apply the appropriate formatting to the heading.
Excel automatically applies all necessary tags when you save a worksheet or workbook in one of the HTML formats. Roughly speaking, the tags break down into two separate categories:
■ Standard HTML tags for coding those parts of the file—the text and its formatting—that a web browser will display.
■ Custom, Office-specific HTML tags for storing document information and application information. For example, when you save a workbook in Single File Web Page format or Web Page format, Excel saves items such as the author’s name and the last author’s name, creation date, and VBA projects using custom HTML tags.
15
Excel’s custom tags should be ignored by web browsers, which don’t care about document items such as name of the person who last modified the document or the application that created the file. These tags are used forround tripping—saving a workbook or worksheet with all its contents, formatting, and extra items (such as VBA code) so that Excel can reopen the file with exactly the same information and formatting as when it saved the file.
If that’s a frown on your forehead, perhaps you’re thinking that round tripping is a technobabble term for what every worthwhile application should be able to do anyway—save files without losing the information they contain. That’s so, but in most cases, applications that create rich content (as opposed to, say, basic text) have used proprietary formats for saving their contents rather than HTML. For example, Excel used to be able to save its workbooks only in the Excel Spreadsheet format. When Excel first gained the capability to create HTML files, it wasn’t able to round-trip fully: the HTML files Excel produced contained only a subset of the data saved in the Excel Spreadsheet format, and if you reopened such an HTML file in Excel, most of the noncontent items would be missing.
But Excel 2003 and the other Office 2003 applications support HTML as a native format alongside their previous native formats. This means that, should you need to, you can save workbooks in HTML instead of the Excel Spreadsheet format, without losing any parts of those workbooks.
Excel can save web pages in two file formats: Single File Web Page and Web Page. Both file types use Office-specific HTML tags to preserve all of the information the file contains in an HTML format. In most cases, you’ll find the Single File Web Page format the better choice, because it creates files that you can easily distribute.
The Single File Web Page format creates a web archive file that contains all the information required for the web page. This doesn’t seem like much of an innovation until you know that the Web Page format (discussed next) creates a separate folder to contain graphics. Files in the Single File Web Page format use the .MHT and .MHTML file extensions.
The Web Page format creates an HTML file that contains the text contents of the document, together with a separate folder that contains the graphics for the document. This makes the web page’s HTML file itself smaller, but the page as a whole is more awkward to distribute, because you need to distribute the graphics folder as well. The folder is created automatically and assigned the web page’s name followed by _files. For example, a web page named My Web Page.htm has a folder named My Web Page_files. Files in the Web Page format use the .HTM and .HTML file extensions.
Save a Worksheet or Workbook As a Web Page
After choosing the appropriate web options for Excel and learning the essentials of HTML and the available file formats, you’re ready to save an existing worksheet or workbook as a web page.
Start by opening the workbook and using Web Page Preview to make sure the page will look okay. To do so, choose File | Web Page Preview. Excel creates a temporary file in your
%userprofile%\ Local Settings\Temporary Internet Files\Content.MSO\ExcelWebPagePreview\
folder, and then displays the page in your default browser (for example, Internet Explorer).
CHAPTER 15: Using Excel’s Web Capabilities 325
HowTo-Tght (8)/ How to Do Everything with Microsoft Office Excel 2003 / Hart-Davis / 3071-1 / Chapter 15 Color profile: Generic CMYK printer profile
Composite Default screen
Check the web page, and then close the browser window. If the page needs changing, make the changes, and then use Web Page Preview again to verify them.
Excel offers you the choice of creating either a static web page (a web page that doesn’t change) or an interactive worksheet that users can manipulate. To manipulate the interactive worksheet, users need to be using Internet Explorer rather than any other browser; but given that Internet Explorer currently enjoys more than 90 percent of the browser market, the chance that any given user has it is high.
Excel also offers you the choice between merely saving the workbook (or a part of it) as a web page andpublishinga copy of the workbook (or the specified part of it). When you publish a copy of the workbook (or a part of it), Excel creates a copy of the workbook or part and saves it under the specified file name, but doesn’t save the workbook itself. So you can publish a copy of an unsaved workbook if you choose.
FIGURE 15-3 Use Web Page Preview to check how a page will look before you save it.
15
CHAPTER 15: Using Excel’s Web Capabilities 327
HowTo-Tght (8)/ How to Do Everything with Microsoft Office Excel 2003 / Hart-Davis / 3071-1 / Chapter 15
To save an Excel workbook, worksheet, or part of a worksheet as a web page, follow these steps:
1. To save a worksheet rather than a workbook, make that worksheet active. To save a range from a worksheet, select that range.
2. Choose File | Save As Web Page to display the Save As dialog box with controls added for creating web pages:
3. In the Save section, select the Entire Workbook option button or the Selection option button to specify whether to save the whole workbook, the active worksheet, or the specified range. If you made a selection before displaying the Save As dialog box, the Selection option button shows that selection (for example, Selection: $C$6:$E$10). If not, the Selection option button appears as Selection: Sheet.
4. To make the web page interactive for Internet Explorer users, select the Add Interactivity check box.
5. Check the title (if any) assigned to the web page:
■ If there is a title, it appears next to the Page Title label.
■ The title is displayed in the browser’s title bar when the browser loads the page.
■ If necessary, click the Change Title button to display the Set Page Title dialog box, enter the text, and click the OK button:
Color profile: Generic CMYK printer profile Composite Default screen
To change the title of a page at any other time than when you’re saving the workbook as a web page, choose File | Properties, change the Title entry on the Summary tab of the Properties dialog box, and click the OK button.
6. If you’re saving the workbook for the first time, follow these steps:
■ Enter the file name in the File Name text box.
■ In the Save As Type drop-down list, choose the Single File Web Page item or the Web Page item as appropriate. (See “Understand HTML, Round Tripping, and Web File Formats,” earlier in this chapter, for an explanation of the differences between the file types.)
■ Click the Save button to save the workbook.
■ Choose File | Save As Web Page again to display the Save As dialog box once more so that you can publish the web page.
7. Click the Publish button to display the Publish As Web Page dialog box (Figure 15-4).
FIGURE 15-4 Choose publication options for an Excel workbook, worksheet, or range in the Publish As Web Page dialog box.
15
CHAPTER 15: Using Excel’s Web Capabilities 329
HowTo-Tght (8)/ How to Do Everything with Microsoft Office Excel 2003 / Hart-Davis / 3071-1 / Chapter 15
8. If necessary, change the item selected in the Choose drop-down list:
■ The choices are Previously Published Items, Entire Workbook, Range of Cells, or the items on any of the worksheets in the workbook. For example, select Items on Drum Kits to have Excel publish all the cells that have contents on the worksheet named Drum Kits.
■ If you selected the appropriate worksheet or range in step 1, the selection here should be correct.
■ If you select the Range of Cells item, Excel displays a Collapse Dialog button that allows you to select a range in the appropriate worksheet manually.
9. In the Viewing Options section, verify the setting of the Add Interactivity With drop-down list. If the check box is selected, you can change the drop-down list between Spreadsheet Functionality and PivotTable Functionality, if necessary.
10. In the Publish As section, check and change the page title, file name, and location, as necessary.
11. Select the AutoRepublish Every Time This Workbook Is Saved check box if you want Excel to automatically publish this web page again each time you save the file. This option is convenient for making sure the web page is always up-to-date, but use it only if you have a permanent and fast connection to the site on which you’re publishing the web page.
Color profile: Generic CMYK printer profile Composite Default screen