Previous Page TOC Next Page



20


Using the Internet Assistants for Excel, Access, and Schedule+


by Ned Snell

The Internet Assistants for Word and PowerPoint both crank out complete, fully fleshed-out HTML documents. None of the other Assistants is really practical for that purpose, which makes perfect sense, because none of the other Office applications for which an Assistant is available—Excel, Access, or Schedule+—is really a document-maker by design. Instead, the Assistants for these applications—which one might group together as "data managers"—help you conveniently export formatted data from an application into an HTML document that you'll otherwise compose and edit in another environment, such as the FrontPage Editor or Word.

Of course, the fact that they're not real document makers doesn't prevent them from trying to be. Each of the Assistants has the ability to produce a self-contained HTML document. But to what end? What good is a Web page full of data without more explanatory—or even conversational—material around it? Excel's Internet Assistant recognizes this, enabling you to export a table of data to an pre-existing HTML document as well as a complete Web page. The others produce entire HTML files populated with their data. But you'll create an HTML file in the Assistants for Access or Schedule+ not because either produces a truly finished Web document, but because doing so gives you a way to channel pre-existing application data into the document. You'll then open that document in the FrontPage Editor, and add all of the other material and formatting that turns a mere datasheet into a page.


NOTE

Internet Assistant is built into all Office 97 applications including Word, PowerPoint, Excel, Access, and Outlook. If you are using earlier versions of these applications, you will need to install their respective Internet Assistants yourself. All of the Microsoft Internet Assistants are included on the CD-ROM bundled with this book. However, it's important that you know how to get the Assistants straight from Microsoft (as described in the following section) because they are updated and enhanced regularly.



Finding and Downloading the Assistants


Anyone not using the 97 edition of Office applications must obtain and install the Internet Assistant software. The latest version of the archive file for each of the Internet Assistants covered in this chapter can be found on each application's Internet Tools page in the Microsoft Office Web Site. To reach the Internet Tools (see Figure 20.1), point your browser to the Office Internet Tools page at

http://www.microsoft.com/MSOffice/MSOfc/it_ofc.htm

From the Internet Tools page shown in Figure 20.1, choose the product name (Excel, Access, or Schedule+) from the bar at the top of the page. For example, click Access to see Access's Internet Tools page, shown in Figure 20.2. The Internet Tools page for each product offers a link to a page about its Internet Assistant. Clicking the link shown in Figure 20.2 displays a page describing Access's Internet Assistant (see Figure 20.3), and offers a link that downloads the Assistant.


NOTE

After installation, any of the Internet Assistants described in this chapter can be uninstalled through the Add/Remove programs icon in the Control Panel.


Figure 20.1. Finding the Internet Assistants from the Office page.

Figure 20.2. The Access Internet Tools page, which features a link to a page about Access's Internet Assistant.

Figure 20.3. The Access Internet Assistant page, which includes installation instructions and a link that downloads the Assistant file.


TIP

With many browsers, you must first download the file, then open it after downloading to extract the archived files and run the Setup Wizard. Some browsers, including Internet Explorer and Netscape Navigator, have the ability to automatically open a file immediately after downloading it, saving you a step (see Figure 20.4).

You can take advantage of this feature to quickly install the Access and Schedule+ Assistants. The Excel Internet Assistant, on the other hand, requires a different setup procedure. You should download the Excel Internet Assistant file and save it to disk, then install it as described in the next section.


Figure 20.4. Instructing Internet Explorer to open a file immediately after downloading it.

Working with Excel's Internet Assistant


Unlike the other Internet Assistants, Excel's is compatible with several different versions of Excel, thanks to the cross-platform and backwards compatibility of Excel's .XLA add-ins. The Internet Assistant add-in, HTML.XLA, works with the following programs:

When using the Assistant, you'll have the option to save data as a complete, new HTML file—including a title, header, and footer you can enter from within the Assistant. Alternatively, you can export an Excel table to a pre-existing HTML file you have prepared with the insertion of a single line of HTML code.


NOTE

The Internet Assistant for Excel does not output equations or other functions—it publishes their results. The finished HTML file shows worksheet output and cannot be dynamically updated as a real worksheet can. To update your Excel-derived Web page, you must first update the worksheet in Excel (or check that it has been updated automatically through OLE), then re-create the Web page through the Assistant.



Setting Up


If you are installing Excel's Internet Assistant yourself, you'll need to copy the Internet Assistant software to your system's Excel library folder. If you use a Windows version of Excel and did not install Excel as part of the Office suite, your library folder is probably




d:\EXCEL\LIBRARY

where d: represents the disk drive where Excel is installed. If you have Office, your Excel library folder is




d:\MSOFFICE\EXCEL\LIBRARY

After HTML.XLA is in the library folder, set up the add-in:

Open Excel and choose Tools|Add-Ins. The Add-Ins dialog appears, as shown in Figure 20.5.

Figure 20.5. Setting up the Excel Internet Assistant Add-In.

In the Add-Ins dialog, click the checkbox next to Internet Assistant Wizard to place a checkmark there, then click OK. The Add-In is installed, and a new selection, Internet Assistant Wizard, appears under the Tools menu.

Selecting Cells to Turn into a Table


Whether you intend to use the Excel Internet Assistant to produce a full page or insert a table into an existing page, your first step is to select the table cells you intend to use.

  1. Begin by opening (or creating) the Excel worksheet and cell data. Perform any text formatting you wish, but keep in mind that your font selections will be ignored. Bold, italic, and underlining are supported.
  2. Select the cells you want included in your HTML table (see Figure 20.6).

    Figure 20.6. Selecting cells to turn into an HTML table.

  3. Choose Tools|Internet Assistant Wizard. A dialog opens like the one shown in Figure 20.7. The cell range shown is the range you highlighted.

    Figure 20.7. Verifying the selected cell range.

  4. Click Next. A dialog like the one in Figure 20.8 is displayed. From this dialog, you must choose whether to insert the table in an existing HTML document, or to create a new HTML document out of the table data. Both approaches are explained in the next two sections.

Figure 20.8. Choosing whether to create a new HTML file or export the table to an existing HTML file.

Making a New HTML Document Out of Selected Cells


To create a new complete HTML document, follow these steps:

  1. Click the top radio button in the Step 2 dialog (see Figure 20.8, earlier in this chapter), then click Next. The Step 3 dialog screen appears (see Figure 20.9).

    Figure 20.9. Entering the page content that surrounds the table.

    In the Step 3 dialog, you can enter the following:

    The sample page that appears on the left side of the dialog illustrates how these elements will be arranged in the finished HTML file.
    Note that all of the entries in the Step 3 dialog are optional. Also, some elements are filled in automatically by the Assistant:
    You can accept these default choices by leaving them alone, or delete them, or replace them with your own entries. Note that you can enter or edit any or all of this information later in the FrontPage Editor.
  2. When finished entering information in the Step 3 dialog, click Next. The Step 4 dialog appears, as shown in Figure 20.10.

    Figure 20.10. Choosing the level of formatting to export.

    In the Step 4 dialog, you choose whether to convert all formatting, including text formatting and cell alignment, into equivalent HTML tags, or whether to export a "bare bones" table that includes only the table and data, but no other formatting. In general, it's best to choose the top button, because browsers that can recognize the formatting tags will apply them, while browsers that can't will simply ignore the tags and display the bare bones anyway. (If you're not sure which way to go, experiment with both options and view the results in browsers you expect your guests to be using.)

  3. Click a radio button in the Step 4 dialog, then click Next. The Step 5 dialog is displayed, requesting a path and filename for the HTML file that will be created.
    By default, the file is saved in the same directory as the worksheet from which the cells were selected, and the filename is the same as the worksheet, except that the filename extension is changed from .XLS to .HTM. You can change to any path and filename you wish, provided you use the extension .HTM or .HTML on the filename.
  4. When the correct path and filename appear in the Step 5 dialog, click Finish.

The file is saved. You can now open it, just as you would any HTML file, in the FrontPage Editor for further editing, or in a browser to view it. Figure 20.11 shows the results of the file created in this example, unedited, as seen through Internet Explorer 3.0.

Figure 20.11. The finished document in Internet Explorer 3.0.

Inserting Selected Cells into an Existing HTML Document


To insert a table of Excel data into an existing HTML document, you begin not in Excel, but in the FrontPage Editor.

Before selecting cells in Excel and starting the Assistant, you must create and save the document into which the Excel table will be inserted. You can compose as much or as little of that document as you wish (you can always continue working on it after you export the table to it from Excel), but you must at least create and save it to disk, and you must insert the following line into the HTML file.




<!--##Table##-->

This line is read as a comment by any browser, and is thus ignored. But the Excel Internet Assistant needs this line to determine where to insert the table.


NOTE

It's important to understand that you can't simply type the line <!--##Table##--> into the body of a document in either the FrontPage Editor or the Word Internet Assistant. If you do, the line is simply considered part of the text of your page. You must enter the line as an HTML tag.

To add an HTML tag in the FrontPage Editor, position the cursor in the document at the spot where you want to insert the tag, then choose Insert|Bot|HTML Markup. Then enter the tag <!--##Table##--> and click OK.

Alternatively, you can enter the tag using Word's Internet Assistant (see Chapter 18, "Using Internet Assistant for Word"). Open or create the HTML document in Word, and make sure it appears in HTML Edit View. Click the point in your document where you want the Excel table inserted, then choose Insert|HTML Markup. In the dialog that appears, type <!--##Table##-->, then click OK.


After preparing your HTML file, follow these steps:

  1. Open Excel, select the table cells, and start the Internet Assistant Wizard as described earlier in this chapter (see Selecting Cells to Turn into an HTML Table).
  2. In the Step 2 dialog (see Figure 20.8, earlier in this chapter), click the bottom radio button, then click Next. The Step 3 dialog is displayed (see Figure 20.12) to remind you to prepare your HTML file first and to collect the filename of the target HTML file.

    Figure 20.12. Identifying the target HTML document into which the table will be inserted.

  3. In the Step 3 dialog, enter the path and filename of the HTML document that contains the <!--##Table##--> comment line, then click Next. The Step 4 dialog appears (see Figure 20.10, earlier in this chapter).
  4. In the Step 4 dialog, you choose whether to convert all formatting, including text formatting and cell alignment, into equivalent HTML tags, or whether to export a "bare bones" table that includes only the table and data, but no other formatting. In general, it's best to choose the top button, because browsers that can recognize the formatting tags apply them, while browsers that can't simply ignore the tags and display the bare bones anyway. (If you're not sure which way to go, experiment with both options and view the results in browsers you expect your guests to use.)
  5. Click a radio button in the Step 4 dialog, then click Next. The table is exported to the target HTML file.

Working with Access's Internet Assistant


Access, as you know, can manage truckloads of data—as many as 65,000 records per datasheet. A Web page, by comparison, is a smaller, simpler data vehicle—nobody wants to see a 65,000-record Web page. So the most important thing to keep in mind when considering building a Web page from Access data is to carve the data into manageable chunks—each fit for a Web page—before running the Internet Assistant. You can phrase queries to extract just the data you want, then base a page on each query. Or you can write reports first, then channel the reports into the Assistant, which creates a separate Web page for each report page.

Note that the Access Internet Assistant exports only formatted data, not any of the decorative or graphical elements that can be included in an Access report. These include any lines or control borders, and also objects created by other applications and imported into Access, such as bitmaps and graphs. As a rule, all you want from Access is its data; you'll provide the decoration yourself, in FrontPage.

Access's Internet Assistant does have a saving grace, though. It includes a collection of templates, HTML files with pre-built headings, rules, backgrounds and so on, to dress up your data. Even if you use a template, you'll want to hop over to FrontPage afterward and fine-tune your document. The templates, however, can give you a healthy head-start. You also can create your own, custom templates for Access data; you'll learn how later in this chapter.

Installing Access's Internet Assistant


If you are not using Access 97, you need to install Internet Assistant yourself. After you download the file IA95.EXE, open the file by double-clicking its icon or by right-clicking it and choosing Open from the context menu. Then step through the Setup Wizard; no input is required, other than that you click whatever you're told to click. When finished, the Wizard either instructs you to restart Windows (after which setup is complete), or it simply reports that setup was completed successfully (in which case you need not restart Windows).


TIP

After you install Access's Internet Assistant, a README.TXT file appears in the folder where the Assistant's files are stored (/ACCESS/IA95). This file contains a brief but helpful user's manual for the Assistant, plus the most recent notes, tips and problem-solvers from Microsoft.



Using Access's Internet Assistant


To create an HTML table from an Access database, follow these steps:

  1. Open the database in Access.
  2. Choose Tools|Add-ins|Internet Assistant. A Welcome screen opens.
  3. On the Welcome screen, click Next. A dialog like the one shown in Figure 20.13 opens.

    Figure 20.13. Choosing objects to export to an HTML document.

  4. In the dialog shown in Figure 20.13, click the checkboxes next to any desired Object Type, then click the checkboxes next to any desired Object Names. You can choose any combination of tables, queries, forms, or reports you wish, and any combination of objects within each. The datasheet of each selected table, query, or form is output as a single HTML document, including only the data objects selected for that Object Type. For reports, a separate HTML file is produced for each page of the report; links to the other pages are added automatically to each page.
    When finished selecting objects, click Next. A dialog like the one shown in Figure 20.14 opens.

    Figure 20.14. Choosing a template.

  5. In the dialog shown in Figure 20.14, you can choose a template to dress up your Access data. To choose a template, click the radio button next to Choose an existing HTML document, then enter the path and filename of the template or Browse for one. To forego a template, click the radio button next to Do not use a template.

    NOTE

    The Access Internet Assistant supports two types of templates: one for datasheets and one for reports. The report templates are identified by the characters _r preceding the .HTM extension. For example, among the templates included with the Access Internet Assistant, the template file STONES.HTM is a datasheet template, while the file STONES_R.HTM is a report template.

    The important difference between the types is that the report version includes built-in links for navigating among the pages of the report. When outputting reports to HTML through the Internet Assistant, choose a report template; when outputting datasheets, use a datasheet template. When outputting both at once, choose a datasheet template—the Assistant automatically switches to the report template when outputting reports, if a report version of the selected datasheet template is available.

    For more about templates, see Creating and Editing HTML Templates for Access later in this chapter.


    When finished choosing (or not choosing) a template, click Next. A dialog like the one shown in Figure 20.15 is displayed.
  6. Enter the path of the folder in which you want your HTML files stored (the default is the folder where the Access Internet Assistant is stored). Click Finish.

Figure 20.15. Choosing a folder for finished HTML documents.

The Internet Assistant generates HTML files from your Access data, and saves the files in the selected folder. The name of each HTML file is automatically derived from the table, query, form, or report on which it is based, followed by the extension .HTM (for example, Customers.HTM).

You can now open the files in the FrontPage Editor for further editing, or view them in a browser.

Creating and Editing HTML Templates for Access


In the procedure for using the Access Internet Assistant (described in the preceding section), you have the opportunity to plug your Access data into a pre-built HTML template. The Assistant includes a number of useful templates, but you should be aware that you can easily create your own, customized templates to serve as receptacles for Access data. All you need to do is build HTML files with special tags coded as placeholders for Access data. When you run the Assistant and choose your template, the Access data is copied into the Web page exactly where you want it.

To create a template, begin by creating a Web page, using any text, images, backgrounds, or other content you desire. Then add the placeholders for the Access data.


NOTE

As mentioned earlier in this chapter, you cannot insert HTML tags into a document in the FrontPage Editor merely by typing them in the document window. To add an HTML tag in the FrontPage Editor, position cursor in the document at the spot where you want to insert the tag, then choose Insert|Bot|HTML Markup. Then enter the tag.

Alternatively, you can enter the tag using Word's Internet Assistant (see Chapter 18). Open or create the HTML document in Word, and make sure it appears in HTML Edit View. Click the point in your document where you want to insert the tag, choose Insert|HTML Markup, then enter your tag.


The placeholder below sets up your template to create an HTML file in which the title is the name of the datasheet object used to create the page. You apply this placeholder in the <TITLE> tag in the header of your HTML file (see the sample HTML file later in this section). If you don't add this placeholder, the name of the template file becomes the title. (You can easily change the title in the FrontPage Editor later, if you wish.)




<!ACCESSTEMPLATE_TITLE>

The placeholder below sets up your template to receive Access data in the body of the HTML file, in the same spot where you put the placeholder.




<!ACCESSTEMPLATE_BODY>

Using the <!ACCESSTEMPLATE_BODY> tag is extremely important. If you have included text, images, or other content in the body of your template file, and you fail to insert the preceding tag, the entire body of the HTML file—including any preexisting content—will be replaced by the Access data. If you include the tag, all preexisting content is left alone, and the Access data is simply added to the body in the spot occupied by the placeholder tag.

Note that you create datasheet templates and report templates the same way, except that you must make two adjustments for report templates. First, a report template must feature the characters _r at the end of its filename (right before the .htm extension). Second, the report template must have additional placeholders that the Assistant needs in order to produce the links between the pages of the report. These placeholders, shown next, must appear within the body of the HTML file anywhere after the <!ACCESSTEMPLATE_BODY> placeholder.




<A HREF="ACCESSTEMPLATE_FIRSTPAGE>">First</A>




<A HREF="ACCESSTEMPLATE_PREVIOUSPAGE>">Previous</A>
<A HREF="ACCESSTEMPLATE_NEXTPAGE>">Next</A>
<A HREF="ACCESSTEMPLATE_LASTPAGE>">Last</A>

The following is a complete Access Template File (for reports) demonstrating where and how the placeholders can be applied.




<HTML>



<HEAD>



<TITLE><!ACCESSTEMPLATE_TITLE></TITLE>



</HEAD>



<BODY>



<H1>See My Data</H1>



<BR>



Below appears data culled straight from an Access datasheet and



plugged into this page by the Access Internet Assistant.



<BR>



<HR>



<BR>



<!ACCESSTEMPLATE_BODY>



<BR>



Choose a link to move to another page of my report:



<BR>



<A HREF="ACCESSTEMPLATE_FIRSTPAGE>">First</A>



<A HREF="ACCESSTEMPLATE_PREVIOUSPAGE>">Previous</A>



<A HREF="ACCESSTEMPLATE_NEXTPAGE>">Next</A>



<A HREF="ACCESSTEMPLATE_LASTPAGE>">Last</A>



</BODY>



</HTML>

To use your finished template, run the Internet Assistant as described earlier in this chapter. When prompted to choose a template, select your template file.

Working with the Schedule+ Internet Assistant


I'll be honest—I can't imagine a scenario under which someone would want to make his or her daily schedule accessible to the Internet masses, or even to intranet mini-masses. But Microsoft thinks that you might want to publish your schedule on your intranet; and in fact, in certain settings, intranet access to the schedules of bosses and/or co-workers could be a big help with planning meetings or assigning tasks. Microsoft offers the Internet Assistant for Schedule+ to make that possible.

Installing Schedule's Internet Assistant


If you are not using Schedule+ 97, you need to install Internet Assistant yourself. After downloading the file SCHIA.EXE, open the file by double-clicking its icon or by right-clicking it and choosing Open from the context menu. Installation is completed automatically, with no further input from you. When setup is complete, the file Schpost.txt opens automatically in Notepad; this is a readme-type file offering a brief manual to the care and feeding of Schedule+'s Internet Assistant.

Using Schedule's Internet Assistant


To create a Web page based on Schedule+ data, follow these steps:

  1. Open the desired schedule file in Schedule+.
  2. Choose File|Internet Assistant. A dialog like the one shown in Figure 20.16 is displayed.

    Figure 20.16. Choosing which parts of your schedule to publish.

  3. Under Publish, click a radio button to choose whether to restrict the page to showing simply when you're free and when you're busy or to show why you're free or busy by including schedule detail.
  4. Under Date/Time Range, pick the hours of the day to include in the page, and the number of weeks to include. The hour range enables you to show your schedule during business hours, for example, but keep your after-hours schedule to yourself.
  5. Under Options, you can enter the HTML title and an e-mail address guests can click to contact you. (Note that you can just as easily add these items in the FrontPage Editor later.) You also can click checkboxes to Include Today's date or Include Private appointments.

    NOTE

    The dialog box shown in Figure 20.16 has a final section, Post to Web, that enables you to post your schedule page directly from the dialog to a Web server. If you're managing your Web with FrontPage Explorer, however, you'll want to ignore this option so that you can properly locate and manage your schedule page through FrontPage, as you do with your other Web documents.


  6. When you are finished, click Save as HTML.

Summary


The Internet Assistants for Excel, Access, and Schedule+ are minor conveniences; you could call them "utilities" if you want. But given that they're free and pretty easy to use, you should keep them in mind as a quick and dirty way to get existing data out of Office applications and into a FrontPage Web.

Previous Page Page Top TOC Next Page