<< . .

. 30
( : 51)



. . >>

3. In Area 1 of the Commands and Options dialog box, you have three options for a
data source for your chart:
• Choose the Data Typed into a Data Sheet option button to enter data to be
charted.
• Click the Data from a Database Table or Query option button to define a
connection to an online database.
• Click the Data from the Following Web Page Item option button to chart data
from an existing embedded spreadsheet. If you choose this option, you can select
from a displayed list of embedded spreadsheets. Use the Range button to define a
graphing range (in the form of A1:D4, for example, to graph cells A1 through
D4). You can name the range (the range name is defined in Excel).
4. Choose a chart type. Click outside the dialog box to display the chart.

Changing chart properties
You can resize a chart by selecting it and using the side or corner handles to change the size.
Other chart properties are defined in the ActiveX Control Properties dialog box.
To change chart properties using the ActiveX Control Properties dialog box, follow
these steps:
Chapter 14 ¦ Integrating FrontPage with Office Applications 337

1. Right-click in the chart and select ActiveX Control Properties from the context menu.
2. Use the options available in the different tabs of the ActiveX Control Properties
dialog box to change chart format or data. For example, use the Data Range area in
the Data Range tab to redefine the data to chart, as shown in Figure 14-15.




Figure 14-15 You can use the ActiveX Control Properties dialog box to redefine
different elements of a chart.


Note
Available chart options will vary depending on the type of chart and the data source.

Controlling charts using the chart menu
If you elect to display the toolbar (in the Show/Hide tab of the ActiveX Control Properties
dialog box), an active toolbar is associated with an embedded chart. The features you
controlled in the ActiveX Control Properties dialog box are now controlled by toolbar icons,
some of which are made available to visitors.
The chart toolbar™s active elements that are available in the FrontPage toolbar are shown in
Figure 14-16.
Part II ¦ Collaborating and Integrating with Office 2003
338




Figure 14-16 Visitors can control calculation, sorting, and formatting.

After you create a linked chart, you ” or visitors ” can enter data in the spreadsheet and
see it graphed in the chart. Figure 14-17 shows a chart working interactively in a browser.




Figure 14-17 Graphing interactively in a browser
Chapter 14 ¦ Integrating FrontPage with Office Applications 339


Presenting a database table in a Web spreadsheet
If you have an Access (or another) database at your Web server, you can present the contents
of a table in that database in a spreadsheet. This first requires that you know how to import
or create a database on your Web site. Very briefly, this can be accomplished by simply
importing an existing database file (Access is the most hassle-free) into your Web using the
File _ Import menu command. Then, click the Add File button in the Import dialog box,
and navigate to your database file. After you select the database and click OK, follow the
prompts to create an online version of your database.
Alternatively, if you want to experiment with connecting a database to a spreadsheet before
you are ready to create an online database, you can use the sample database that comes with
FrontPage.
With a database imported into FrontPage (or using the sample database), follow these steps
to display the content of that database in a Spreadsheet Web component:
1. Open a new page in FrontPage. You will use this page to display the database
records in a database region.
2. Choose Insert _ Database _ Results.
3. If you have an imported (or FrontPage-generated) database, click Use an Existing
Database Connection and choose your installed database from the Use an Existing
Database Connection drop-down list. Alternately, click the Use a Sample Database
Connection (Northwind) radio button.

This step-by-step set of instructions zips past many database options, focusing instead on just
Note
creating a simple database region on which to base a spreadsheet.

4. Click Next. In the Step 2 Wizard dialog box, choose a table from the Record Source
drop-down list. Choose a table to display in your Web page database region.
5. Click Next. Accept the defaults in the Step 3 and Step 4 Wizard dialog boxes by
clicking Next.
6. In the Step 5 Wizard dialog box, choose the Display All Records Together option
button. Click Finish to generate a database region displaying the data from your
database. A database region is shown in Figure 14-18.
Part II ¦ Collaborating and Integrating with Office 2003
340




Figure 14-18: This database region presents data that can be displayed in a linked
spreadsheet.


You can generate a database region only if you are connected to a Web server with FrontPage
Note
extensions. In addition, you won™t see the actual data until you preview your page in a Web
browser.

7. Save the page with the database region as an .asp file page, and then use it as a
base for spreadsheet data. Start by choosing File _ Save As, and choose Active
Server Pages from the Save As Type drop-down list in the Save As dialog box. Give
the .asp file a name in the File Name box, and click OK.

In this example, because we started with a blank page, FrontPage assigns an ASP filename
Note
extension by default. However, that doesn™t always happen if the page isn™t blank, so make a
point of assigning ASP file format to this page.

8. Create a new Web page (or open an existing one), and choose Insert _ Web
Component _ Spreadsheets and Charts. Click Office Spreadsheet in the Choose a
Control list of the Insert Web Component dialog box.
9. Click Finish to generate a spreadsheet. In the spreadsheet component, click the
Commands and Options icon to open the Commands and Options dialog box.
Chapter 14 ¦ Integrating FrontPage with Office Applications 341

10. In the Import tab of the Commands and Options dialog box, choose HTML from the
Data Type drop-down list, and enter the URL for your page (the one you saved in
Step 7), as shown in Figure 14-19.




Figure 14-19 This spreadsheet component has been associated with a Web page
with a database region.


Entering the absolute URL (that is, the entire URL, including http://www) seems to work
Note
best.

11. To continually update the spreadsheet, click the Refresh Data from URL at Run
Time checkbox.
12. Click Import Now. The spreadsheet will display the content of the associated
database region.

Creating Office PivotTables
Of the three interactive Office Web components that you can use in a Web page, PivotTables
are the most complex. PivotTables themselves are fairly complicated. A full discussion of
PivotTables is beyond the scope of this book, but in short, PivotTables summarize data from
a table. Therefore, for example, if you have a list of 500 orders for 12 products and the dates
the orders were placed, a PivotTable could summarize how many orders had been placed for
each of the 12 products. Or, the PivotTable could be used to total how many orders were
placed each day.
Assuming that you and your visitors are comfortable designing and manipulating fields in a
PivotTable, you can create an interactive PivotTable that summarizes data in an Excel file or
Access database table.
Part II ¦ Collaborating and Integrating with Office 2003
342


Connecting a PivotTable to an Excel data source
Steve Martin used to do a comedy routine around the theme of “how to make a million
dollars and not pay any taxes.” Part of the joke was that his starting point was “go get a
million dollars,” and then he would fill you in on the rest. The story™s relevance to
connecting a PivotTable to an Excel data source is that PivotTables are a rather complex art,
and an advanced spreadsheet skill. If you™re already comfortable with them, we can show
you how to plug them into a FrontPage Web site.
Even if you are comfortable with PivotTables, connecting a PivotTable to an Excel data
source is not a simple process. The basic process involves first connecting an existing Excel
file to your site as a recognized Web database, and then generating a PivotTable from a
named range in that file.
To generate an interactive online PivotTable from an Excel spreadsheet, follow these steps:
1. Create or open in Excel a worksheet that has the information you want to summarize
in your PivotTable.
2. Select the data and then choose Insert _ Name _ Define.
3. Assign a range name (for example, “Data”). You may want to jot down the range
name, because you™ll need it again in a later step.
4. Save the Excel file and note the filename and folder to which it is saved.
5. In Page view, open the FrontPage Web page in which you will insert the PivotTable.
6. Select Insert _ Web Component _ Spreadsheets and Charts. Choose Office
PivotTable, and click Finish. A blank PivotTable appears in Page view.
7. Click the Commands and Options button (the only active button in the PivotTable
toolbar) to open the Commands and Options dialog box, shown in Figure 14-20.




Figure 14-20: Buried in the PivotTable Commands and Options dialog box are the
elements needed to connect your PivotTable to a data source.
Chapter 14 ¦ Integrating FrontPage with Office Applications 343

8. Choose the Data Source tab in the Commands and Options dialog box. Click the
Connection radio button, and then click the Edit button. The Select Data Source
dialog box appears, as shown in Figure 14-21.




Figure 14-21 Choosing an Excel file to link to your Web PivotTable

9. Click the New Source button. You are about to connect your Excel file with your
Web site. The Data Connection Wizard opens to walk you through that process.
10. Choose ODBC DSN as your database source. Click Next.
11. Choose Excel files as your data source, and click Next.
12. Navigate to your Excel Workbook in the Select Workbook dialog box. Choose your
Excel file and click Next.
13. The Data Connection Wizard displays available named ranges (also referred to for
these purposes as “tables”) in your selected spreadsheet. Select one of these tables
and click Next.
14. In the final Wizard window, enter a description and keywords to help identify and
locate the PivotTable. These are optional. After you enter a description and
keywords, click Finish. You will be returned to the Select Data Source dialog box,
where your newly defined database connection (to your spreadsheet) is now one of
the connection options. Click Open to connect your selected spreadsheet to the
PivotTable.
15. Your PivotTable is now connected to your spreadsheet, and is ready to have fields
added. Click the Field List icon in the PivotTable toolbar to display field names, as
shown in Figure 14-22.

For a brief step-by-step explanation of how to put fields into a PivotTable, see the section
Note
“Adding fields to a PivotTable” later in this chapter.
Part II ¦ Collaborating and Integrating with Office 2003
344




Figure 14-22: Once your PivotTable is connected to a data source, you can
add fields.

16. Drag fields into place in your PivotTable.
17. Save your page. Your PivotTable is now ready to be both accessed by visitors to
your Web site and utilized to synthesize table data, as shown in Figure 14-23.




Figure 14-23 A completed PivotTable in FrontPage
Chapter 14 ¦ Integrating FrontPage with Office Applications 345


Defining a PivotTable
Here is a quick summary of how to put a PivotTable together:
After your PivotTable control is connected to an Excel data source, you can use the Field List button
in the PivotTable toolbar to add fields to the PivotTable displayed. Every PivotTable requires at least
one Row or Column field and at least one Total or Detail Field.
The basic concept is to summarize data by sorting it into categories. For example, if you wrote
books for a dozen publishers over the past four years, you could produce a PivotTable listing how
many books you wrote for each publisher each year by making Year the column field, Publisher the
Row field, and Books Written the Detail field.

Adding fields to a PivotTable
To add fields to a PivotTable, follow these steps:
1. Click the Field List button in the PivotTable toolbar to display a list of fields in your
database.
2. Drag one of the fields into the Drop Column Fields Here area of the PivotTable, and
drag one field into the Drop Row Fields Here area, as shown in Figure 14-24.




Figure 14-24: Adding fields to a PivotTable
Part II ¦ Collaborating and Integrating with Office 2003
346

3. You must have at least one field in the Detail area (in the middle of the PivotTable),
so drag a field from the Field list into the middle of the PivotTable.

The field in the Detail area normally displays values. These values can be summed, counted, or
Note
have other calculations performed on them.

4. You can drag a field into the Drop Filter Fields Here area at the top of the PivotTable
to create a filtering drop-down list that will control what is displayed in the entire
PivotTable. This field is optional and simply provides a higher level of filtering in
addition to the options you already have in the PivotTable.
5. After you define your PivotTable, close the PivotTable Field List dialog box. Note
that each field has a drop-down list associated with it. Use the checkboxes in these
drop-down lists to filter your PivotTable results, as shown in Figure 14-25.




Figure 14-25: Filtering a PivotTable enables you to fine-tune your analysis of data.
Chapter 14 ¦ Integrating FrontPage with Office Applications 347

6. You can remove fields by right-clicking them and selecting Remove Field from the
context menu.
7. To calculate (count, sum, find maximum or minimum value), right-click a field in
the Detail area and select AutoCalc. Then choose from calculation options such as
sum, count, or average.
8. You can turn subtotaling on or off for fields where it applies by right-clicking on a
field in the PivotTable and selecting or deselecting Subtotal from the context menu.

Formatting and calculating PivotTable data
After you define your PivotTable, save your Web page and preview it in Internet Explorer to
test it. Figure 14-26 shows an interactive PivotTable in Internet Explorer.




Figure 14-26: Visitors can do their own synthesis of your data with a PivotTable in
Internet Explorer ” as long as they have Office 2003 installed.
Part II ¦ Collaborating and Integrating with Office 2003
348

In FrontPage, you can format, calculate, and sort PivotTable data by using the PivotTable
toolbar, by right-clicking and choosing context menu options, or by using the Commands
and Options button in the PivotTable toolbar to open the Commands and Options dialog
box.
Some, but not all, of these filtering, sorting, formatting, and calculating features are
available for visitors when they work with the PivotTable in a Web site. You can control
table protection using the ActiveX dialog box associated with your PivotTable. This
process is the same as for the ActiveX dialog box associated with a spreadsheet, explored
earlier in this chapter.

Implementing an Office spreadsheet Web component
In this tutorial, you™ll add a spreadsheet to a Web page.
1. Open an existing FrontPage Web or create a new one. Open a Web page in Page
view.
2. Enter the title See How Much of Your Time You Spend Commuting on the page,
and then press Enter.
3. Select Insert _ Web Component _ Spreadsheets and Charts, and choose Office
Spreadsheet. Click Finish.
4. Click in cell A1 of the spreadsheet and type How many hours do you spend
commuting? Press Enter.
5. In cell A3 of the spreadsheet, enter =A2/24.
6. Click the Commands and Options button in the spreadsheet. In the Format tab, select
Percent from the Number Format drop-down list.
7. Click in cell A2. In the Commands and Options dialog box™s Format tab, deselect
the Lock Cells icon.
8. In the Protection tab of the Commands and Options dialog box, select Protect Active
Sheet.
9. Save the file, and preview it in Internet Explorer.
10. While testing the spreadsheet in Internet Explorer, attempt to enter text in cell A1.
Try to enter a number in cell A3. You should see a warning like the one shown on
the bottom of the screen in Figure 14-27.
Chapter 14 ¦ Integrating FrontPage with Office Applications 349




Figure 14-27: You can lock cells in a spreadsheet so that visitors can enter data
only in cells that you designate to accept input.



From FrontPage to Office 2003
The discussion thus far has focused on how to create Web page content in Office and
transfer it into FrontPage Web pages. You can also collect information in FrontPage and
send it to text or spreadsheet files that are stored at your Web server.

Collecting data from input forms requires some advanced FrontPage skills that are covered in
Note
Chapter 17 of FrontPage 2003 Bible. This section takes only a quick look at input forms from the
perspective of collecting data that can be used in a spreadsheet or text file.

Even before you examine how to create your own custom input forms, you can begin to
experiment with the Feedback Form page template, which contains a pre-made input form.
Part II ¦ Collaborating and Integrating with Office 2003
350


Sending data to Word mail-merge files
You can create an input form by using the page template with an input form (using the
Feedback Form page template, for example). With a Web open, select File _ New _ Page,
and double-click one of the templates with an input form. The input forms are filled with
different text and input fields and are surrounded by a dashed line.
To create an input form that sends data to a .doc file, follow these steps:
1. With a form on your page, right-click anywhere in the form (within the dashed lines)
and select Form Properties from the context menu. The Form Properties dialog box
appears.
2. Click the Send To option button and enter a filename with a .doc filename
extension (for example, maillist.doc).
3. After you name the target file (the .doc filename extension is important), click the
Options button in the dialog box and pull down the File Format list. Choose Text
Database Using Tab As a Separator, as shown in Figure 14-28.

Make sure you retain the .doc filename extension. FrontPage will try to change your filename
Note
extension to .txt when you choose the Text Database Using Tab As a Separator format.




Figure 14-28: Sending input to a Word file

4. Click OK.
5. Save your Web page.
You can test your input form by clicking the Preview in Browser button and entering
information in the input form. After you do, click the Submit button.
Chapter 14 ¦ Integrating FrontPage with Office Applications 351

You will see your .doc file in Folder view (you may have to press the F5 function key to
refresh the Folder view). As data is saved to your .doc file, you can open the file in Word
by double-clicking it. With fields separated by tabs, you can use this file as a mail-merge
data file in Word.

Sending data to Excel
You can save data to files that will open in Excel by using the same procedure previously
outlined for saving to a Word file. The only difference is that your filename should have an
.xls extension (for example, Feedback.xls). When you save tab-delimited text to an
Excel file, you can open that file in Excel by double-clicking it.

Sending reports to Excel
With FrontPage 2003, you can save reports as HTML files and then open them in Excel for
printing, graphing, sorting, or other analysis.
To save a report as an HTML page, view the report (select View _ Reports and choose any
report except for Summary). With the Report in view, choose File _ Save As, and save the
file as an HTML file to any folder on your Web site or your local computer.

<< . .

. 30
( : 51)



. . >>