Using the Internet to Publish Results

---

A Web server can be used as a repository of information, storing your application's results and presenting them to a wider audience than can be achieved with printed reports. By presenting results as Web pages, the reader of those pages can easily use the results as sources of data for his own analysis and easily pass those results to other interested parties.

Setting Up a Web Server

There are entire books written on this subject. Consider this section a brief overview of how to set up a Web server. You can find more information about setting up a Web server in Windows 2003 Server in Microsoft Windows Server 2003 Administrator's Companion, from Microsoft Press. For information on setting up a Web server in Windows XP Professional, see Microsoft Windows XP Networking Inside Out (also from Microsoft Press).

The examples that you'll review in the following sections will require write access to a Web server. To host Web pages, you must first install Internet Information Server (IIS). This can be done using the Add or Remove Programs applet found in the Control Panel. Once IIS is installed, the Management Console can be found within the Administrative Tools folder in the Control Panel. Using the Console, you can manage various aspects of your Web server.

The Home Directory is used to store your Web page. To configure the properties of your Web Servers Home Directory, Open IIS, and right-click the Default Web Site node. Select Properties, and click the Home Directory tab. You'll be presented with various configuration options for the default Web site. Make sure that the Read and Write check boxes are selected, and click OK. See Figure 25-6. This ensures that you are able to post new content to the folder.

Figure 25-6: The Default Web Site Properties dialog box is used to configure IIS. To post Web pages to your Web server, ensure a check mark is placed in the Read and Write check boxes.

Notice the Local Path box. This is where the root of your Web server is located. By default, the path is C:\inetpub\wwwroot. Any Web pages placed in this directory are published at the following URL: http://localhost/PageName.html.

Saving Worksheets as Web Pages

To help save time and effort, it's best if a template is created for updating the latest information to your Web site. The template provides the easiest way to present your results on a Web page. The template workbook contains all the formatting and links that are required. When your application produces its results, it's a simple task to copy the relevant numbers into the template, and then save the template directly to the Web server.

The following example uses a template file to post the new results directly to the Web server:

Tip 

Customize the Procedure
The following procedure will need to be customized using a folder you have created on your computer. Keep in mind that to run this procedure you'll need to modify filenames, range references, references to charts, and PivotTables.

Sub PublishResultsToWeb()
 
    Dim webBk As Workbook
    Dim webSht As Worksheet
    
    Set webBk = Workbooks.Add("C:\WebTemplate.xls")
    Set webSht = webBk.Worksheets(1)
    
    webSht.Range("Profits").Value = Workbooks("Results.xls") _
        .Worksheets("Financials").Range("Profits").Value
        
    webSht.SaveAs "http://localhost/resultsjuly2001.htm", xlHtml
    
    webBk.Close False
 
End Sub

Adding Interactivity with the Web Components

The previous example saved a static rendition of the worksheet in HTML format to the Web server. However, Office Web Components can create interactive Web pages. When saving a worksheet in interactive form, several conversions occur:

• Worksheet or ranges on the sheet are converted to Spreadsheet Web Components

• Selected Charts are converted to Chart Web Components

• PivotTables are converted to PivotTable Web Components

These components are ActiveX controls that are embedded in the HTML page, designed to provide similar interaction as in Excel but from the browser.

It's beyond the scope of this book to document the Web Components, but the following code can be used to save a workbook as an interactive Web page, where the workbook contains a range of data to be published in a specified range, a PivotTable, and an embedded chart.

Tip 

Customize Your Procedure
Keep in mind that to run this procedure you'll need to customize the PublishInteractivePage procedure to reference your Web server, the correct filenames, and the worksheet name you have set up for this example.

Sub PublishInteractivePage()
 
    With ActiveWorkbook.PublishObjects
        .Delete
        .Add(xlSourcePivotTable, "http://localhost/page.htm", "Sheet1", _
            "PivotTable1", xlHtmlList).Publish True
        .Add(xlSourceRange, "http://localhost/page.htm", "Sheet1", _
            "A1:C30", xlHtmlCalc).Publish False
        .Add(xlSourceChart, "http://localhost/page.htm", "Sheet1", _
            "Chart 1", xlHtmlChart).Publish False
    End With
 
End Sub

Tip 

Expanding Your Web Page
To publish workbook components to individual Web pages, save the individual objects to a new page.

The resulting Web page is quite simple because only placeholders for the various Web Components are added. Now that the page has been created and contains the Web Components, modify this page so that it's presentation quality.

Microsoft Office Web Components provide the means to make it possible for you to publish Office documents to the Web while preserving the interactivity the documents have when they are viewed in their native applications. The Office Web Components are a collection of ActiveX controls. When Microsoft Office users view a Web page that contains an Office Web Component, they can interact with the data displayed in that document right in Microsoft Internet Explorer. Users can sort, filter, add, or change data, expand and collapse detail views, work with PivotTable lists, and chart the results of their changes. In addition, the Office Web Components are fully programmable, which makes it possible for you create rich, interactive content for Web-based applications.

Note 

Office Web Components only work in Internet Explorer 4.01 or later, and the Microsoft Access data access pages work only in Internet Explorer 5 or later. In addition, you get the most complete functionality with all the Office Web Component controls in Internet Explorer 5 or later.

Communicating with a Web Server

Within a corporate network, nearly all data transfer takes place using proprietary binary formats, ranging from transferring files to performing remote database queries. Due primarily to security considerations, communication across the Internet has evolved to use textual formats, the simplest being a URL, such as http://mysite.com/mywebpage.html.

To communicate with an application running on a Web server, you need to be able to perform some processing on the server and pass information to and receive information from that application.

The Workbook object's FollowHyperlink method can be used to communicate with a Web server. There are a few problems with using this method, including the following:

• If an error occurs during the connection, Excel will freeze.

• Any data returned from the hyperlink is automatically displayed as a new workbook.

• You have very little control over the communication.

There are more flexible alternatives provided by the Microsoft Internet Transfer Control, msinet.ocx. This ActiveX control, often referred to as the ITC, is an easy-to-use wrapper for the wininet.dll file, which provides low-level Internet-related services for the Microsoft Windows platform.

There are two mechanisms that can be used to send information to a Web server. You can either include the information as part of the URL string, or you can send it as a separate section of the HTTP request.

URL Encoding uses parameters included with the URL string by appending them to the end of the URL. You've probably noticed as you surf through Web pages that after you type the address, the address bar automatically updates to the destination URL with question marks and equal signs, with several letters thrown into the mix. Next time, pay attention to the character string. Upon closer examination, you'll see that after the URL there's a question mark followed by param1=value&param2=value. For example, when you navigate to the MSN Hotmail Web page, you type in the address, www.hotmail.com. However, after you press Enter, the following result appears in the address bar:

http://loginnet.passport.com/login.srf?id=2&svc=mail&cbid=24325
&msppjph=1&tw=0& fs=1&fsa=1&fsat=1296000&lc=1033&_lang=EN

That is certainly different from what was initially typed in, but somehow you reach the destination Web site.

One advantage to including the parameters as part of the URL is that the URL with the parameters can be stored as part of the user's favorites. However, a URL is limited to 2083 characters in Internet Explorer, which in turn restricts the amount of information that can be passed using this method.

If you choose to send information as a separate section of the HTTP request, you will be using the POST field to transfer the information. POSTing data uses the POST field to send information to the Web server. Because there's almost no limit to the amount of data that can be put in a POST field, it's the preferred way of transferring information to the server.

How do you return information to the client? The data can be presented to the client as a Web page that can be read using the same techniques described throughout this chapter.

---