< Day Day Up > 

The Hypertext Markup Language (HTML) is the standard markup language used for documents on the World Wide Web. HTML uses tags to indicate how Web browsers should display page elements such as text and graphics and how to respond to user actions.

The concept of publishing Excel spreadsheet data on the World Wide Web makes a lot of sense, both from the standpoint of the worksheet's tabular layout and the worksheet's calculated contents. Anyone who has coded an HTML table can tell you that this is a tedious task. Even creating the simplest HTML table is time consuming because you have to use <TH> and </TH> tags to set up the column headings in the table, along with <TR> and </TR> tags to set up the rows of the table, and <TD> and </TD> tags to define the number and width of the columns, as well as what data goes in each cell of the table.

Saving a Worksheet as a Web Page

Saving a Worksheet as a Web page is a simple task in Excel. You can set up your worksheet with the appropriate data, charts, and PivotTables. Once the setup is complete, you can save your worksheet as a Web page.

Excel enables you to create Web pages that display your worksheet data either in a static, 'look but do not touch' mode, or in an interactive, 'have some fun' mode. When you create a Web page with static worksheet data, your users can view the Excel data only with their Web browsers. However, when you create a Web page with interactive worksheet data, your users can continue to play around with the data by editing and formatting its values. Depending upon the nature of the spreadsheet, your users can even continue to perform calculations and, in cases of data lists, manipulate the data by sorting and filtering it.

There are many considerations before you save your workbook or worksheet as a Web page. For example, you'll need to decide if you plan to save a portion of a workbook or a part of a worksheet. When you decide to save a part of a worksheet, you should select the cells before you start the save process. You aren't limited to saving a range of cells-you can save a chart as a Web page, as well.

When choosing the location to place the Web page, you should consider whether it will be stored on your local hard drive or network drive, or whether you want to post the Web page directly on your company's Internet or intranet Web site. To save the new page on a File Transfer Protocol (FTP) site, select FTP Locations from the Save In drop-down list box and then open the FTP folder in which you want the page saved. The Web folders or FTP locations must be set up before you can save your worksheet Web pages to these locations. You can also add a title, which appears centered at the top of the page before any of the data or charts. This isn't the only time you can specify your Web page title; you can add or edit the Web page title after the page is created.

To create a static Web page, you follow these general steps:

  1. Open the workbook with the data to be saved as a Web page.

  2. Click the File menu and select the Save as Web Page option, as shown in Figure 25-1.

    click to expand
    Figure 25-1: The Save as Web Page dialog box is similar to the Save dialog box with several enhancements for Web page functionality.


    Select What to Save
    If you know that you want to save a particular chart or particular range of cells of a worksheet in the new Web page, you should select the chart to be saved before you open the Save As dialog box. By selecting a particular chart or a range of cells, you will be able to save only the required information to your Web page. Selecting the chart ahead of time changes the Selection: Sheet option button to the Selection: Chart option button. In the case of a cell range selection, the Selection: Sheet option button changes to a Selection: option button, followed by the address of the cells selected.

  3. Specify the location where the Web page is to be saved.

  4. Indicate which part of the workbook is to be saved in the new Web page. To save the contents of all sheets in the workbook, make sure that the Entire Workbook option button is selected. To save only the data in the current worksheet, choose the Selection: Sheet option button instead. Remember, if you clicked the chart in the worksheet that you intend to convert into a Web page graphic before opening the Save As dialog box, you need to choose the Selection: Chart option button, which replaces the Selection: Sheet button. If you selected a cell range, you need to choose the Selection: option button, followed by the address of the selected cell range. If you want to save the contents of a worksheet other than the one currently selected, you can specify this later.

  5. Type the name for the new Web page in the File Name text box.


    Notice that Excel appends the filename extension .htm to whichever filename you enter in this text box. This is the standard extension for a HyperText Markup text file. If you plan to publish the Web page on a UNIX Web server, keep in mind that this operating system is sensitive to uppercase and lowercase letters in the filename. The Macintosh and Windows operating system are both case blind when it comes to filenames.

  6. Specify a title for the Web page; click the Change Title button in the Save As dialog box. Type the text for the title in the Set Page Title dialog box, and click OK.


    The Procedure Depends on You
    To save a chart that wasn't selected prior to opening the Save As dialog box, click the Publish button and then select the chart, identified by its description from the Choose drop- down list. To save a specific range of cells that you didn't select prior to opening the Save As dialog box, click the Publish button. Then select Range of Cells from the Choose drop- down list before you type the range address in the text box immediately below, or enter the range by selecting the range of cells by highlighting them in the worksheet.

While saving your worksheet data in the new Web page, Excel automatically creates a new folder with the same name as the .htm file that contains all the supporting files, including the graphics files and charts among the numerical data. So if you move the Web page from a local drive to a Web server, you need to copy the supporting files folder, as well as its Web page file, to ensure that the user's browser can successfully open the entire contents of the page.

If you don't want Excel to create a separate folder with the supporting files, change the setting in the Web Options dialog box. To open the Web Options dialog box, select the Tools menu from Microsoft Excel and then select Options. Select the General Tab, and click the Web Options button. In the Web Options dialog box, select the Files tab. Remove the check mark from the Organize Supporting Files in a Folder check box on the Files tab.


Keep in mind that when you save an entire workbook containing worksheet data and charts on separate worksheets, Internet Explorer preserves the original Excel sheet arrangement in the resultant static Web page by adding sheet tabs at the bottom of the Internet Explorer window.

Alternatively you can save your worksheet as a Web page using Microsoft Visual Basic for Applications (VBA) code. Review the following simple procedure to save your active worksheet as a Web page:

Sub SaveAsWebPage()

With ActiveWorkbook.PublishObjects.Add(xlSourceSheet, _
"C:\Page.mht", "Sheet1", "", xlHtmlStatic, _
"ProductSales_18739", "My Web")
.Publish (False)
.AutoRepublish = False
End With
End Sub

This procedure simply saves the active worksheet as a Web page. Remember that thorough testing should be included whenever a new Web page is created. Testing will alleviate end user problems before your page is published on the Web.

Publishing a Worksheet to the Web

After the Web page has been saved, you are required to publish the page to the Web before you can share it with the world. The following steps review how to post your Web page on the Internet:

  1. Click File, Save as Web Page to open the Save As Web Page dialog box. Select the Entire Workbook option. Then click the Publish button. The Publish as Web Page dialog box opens, as shown in Figure 25-2.

    click to expand
    Figure 25-2: The Publish as a Web Page dialog box displays the options available before the save operation is complete. You can set up the location, file name, and other options from this dialog box.

  2. Click the Browse button to select the location for the Web page to be stored.

  3. If you have already set up a Web folder, navigate to the appropriate folder and skip forward to step 10; otherwise, follow these steps.

  4. To create a new Network Place, click My Network Places in the Save In list and click Add a Network Place. The Add Network Place Wizard window opens, as shown in Figure 25-3.

    click to expand
    Figure 25-3: The Add Network Place wizard will guide you through creating a new network location for your Web page.


    You can return to Excel when you have completed the setup of the new network location.

  5. Click the Next button to accept the default, Create a Shortcut to an Existing Network Place. Then type your network location in the Location field, such as Click the Finish button to complete the wizard.

  6. Enter your username and password so the Web server can authenticate you.

  7. Type the file name, and click OK. You'll be returned to the Publish as Web Page dialog box.

  8. Click the Publish button.


    Remember the URL displayed in the Filename text box.

  9. Open the Web Page in your Web Browser.

When you have completed the publication of your Web page, make sure that you test the functionality of the file. The testing phase should be thorough to avoid any complications when users begin to interact with the file. It's good practice to test all links and interactivity to ensure you have obtained the functionality you were striving for.

Now that you understand the Web publishing process, it's quite simple to add the publication step to the procedure you created earlier. The following procedure has been modified so that the Web page is published after it has been created:

Sub SaveAsWebPage()

With ActiveWorkbook.PublishObjects.Add(xlSourceSheet, _
"C:\My Webs\MyPage.mht", "Sheet1", "", xlHtmlStatic, _
"ProductSales_18739", "My Web")
.Publish (True)
.AutoRepublish = False
End With

End Sub

start sidebar
Inside Out
Web Page Style and Publication

Keep in mind that whether you are positing your data so that it's available to your intranet or to the world on the Internet, you are sharing information. This information should be presented on the screen in an easy-to-read format. Displaying a table of information is great, but a chart might be easier for the audience to quickly digest.

There are also several general rules of thumb to follow when you create your Web site. For example, I am sure that you've visited a Web site that was difficult to read because of the color selection. If a site has a dark background and a dark font color, it might be too frustrating for the audience to stay long enough to read your information. The page layout is also important, in addition to using appropriate, user-friendly navigational tools. Remember that users will become frustrated if you have your information laid out well but it's inconvenient to return to previous pages or the top of the current page.

Finally, before you publish your Web page, you should make sure it's ready by checking for broken hyperlinks, verifying that the pages look the way you want them to, and testing the Web to make sure that everything works. A good way to ensure your Web is ready is to preview it in more than one Web browser.

end sidebar

Making Web-Based Worksheets Interactive

Interactive components allow people to manipulate your data on the Web page in the browser. For example, you can publish an interactive spreadsheet that calculates loan information. A user can browse through the page and enter financial information such as the loan amount and interest rate to calculate his monthly payment.

The interactive components used in the HTML file can't be opened and modified in Excel, so you should maintain a master copy of the Excel workbook from which you published, so that you can make changes to it and republish the workbook if necessary.

When a Web page is published with interactivity, users can manipulate data. You create an interactive Excel Web page by saving the data with spreadsheet functionality. When you publish interactively with spreadsheet functionality, users can do the following:

  • Enter data

  • Format data

  • Calculate data

  • Analyze data

  • Sort and filter

There are several types of interactivity available to your worksheet. Table 25-1 outlines the available options.

Table 25-1: Interactivity Options



Spreadsheet functionality

Users can modify the data by changing the values in the cells. The formulas are then automatically updated to reflect the changes. This option also allows you to filter lists on the Web page.

PivotTable functionality

Users can change the layout of rows and columns to see different summaries of the source data if using a PivotTable report. Using PivotTable also allows the user to update the external data range.

Chart functionality

Provides the user with interactive options with the chart on the Web page.


The browser used to display the interactive components must be Microsoft Internet Explorer 4.1 or later and have an appropriate Microsoft Office license to work with spreadsheets, charts, or PivotTable lists published interactively from Excel.

The steps below outline how to add interactivity on your Web page.

  1. Select the File menu, and then select the Save as Web page menu option.

  2. Type the filename in the Filename text box.

  3. Place a check mark in the Add Interactivity check box.

  4. Click the Publish button.

  5. Select the type of interactivity you want from the Add Interactivity With list. The type of interactivity that's available is limited to the types of objects in your worksheet.

  6. Click the Publish button.


    You will be able to open and use interactive Web pages in your Web browser. However, if you need to modify the data on the Web site, you won't be able to open and modify interactive Web pages in Excel. You should store a backup copy of the original workbook you published, in case you need to modify the data. If changes are made to the original spreadsheet, you'll need to republish and set up the interactivity with the new Web page.

 < Day Day Up > 

Microsoft Excel 2003 Programming Inside Out
Microsoft Office Excel 2003 Programming Inside Out (Inside Out (Microsoft))
ISBN: 0735619859
EAN: 2147483647
Year: 2006
Pages: 161

Similar book on Amazon © 2008-2017.
If you may any questions please contact us: