Not Your Mother's Office
August 5, 1999
It seems like you can't turn around these days without bumping into the Internet. Of course, I'm speaking metaphorically. Take a look at the other MSDN Online columns, and you will see my point. You've got Web Men Talking, DHTML Dude, Extreme XML, the Scripting Clinic, and so on.
Frankly, I'm starting to feel a little left out. After all, I talk about Microsoft Office development. You've got to ask yourself what does Office development have to do with Internet technologies? The answer is: plenty.
Discussions about the Internet and Internet technologies are everywhere. These discussions encompass such a huge range of technologies that it's hard to come up with a term to accurately describe them. But that won't stop me from trying. I'm going to use a precise term to refer to Internet-related technologies that are of interest to Office developers: Web stuff.
In this month's column, I'm going to talk about Office Web stuff and point you to some additional resources you can use to take full advantage of working with Office to create documents designed to be viewed in a browser.
Office Web Stuff
Several features in Microsoft Office 2000 make it easier to create documents designed to be viewed using a browser:
- Office documents now support HTML as a native file format.
- You can edit HTML and add and debug Microsoft Visual Basic® Scripting Edition (VBScript) or Microsoft JScript® in an Office document using the new Microsoft Script Editor.
- Office includes the Office Web Components -- ActiveX® controls that allow you to export to Web pages much of the data access, spreadsheet, pivot table, and charting functionality found in Office applications.
- In Microsoft Access, you can create data access pages that easily allow you to display and work with bound data using only Microsoft Internet Explorer.
- Office includes the Office Server Extensions, which you can use to create threaded discussions on Web pages viewed in Internet Explorer. The Office Server Extensions also provide a fully programmable object model that lets you work with these threaded discussions by using Visual Basic for Applications (VBA).
The Web stuff in Office 2000 gives developers a host of new features for creating custom solutions that take full advantage of Web-based information sharing and collaboration. The time has finally come where you really can think of Office as an easily accessible set of Web-publishing tools that enable users to manage information instead of documents.
Office Web Components
The Office Web Components are a collection of four ActiveX controls. They are the Spreadsheet, Chart, Pivotlist, and DataSource controls, and they are designed to let you publish fully interactive worksheets, charts, PivotTable reports, and databases on the Web. The Office Web Components are not limited to use on the Web; they can be used with any container that supports ActiveX controls, such as Access Forms and Reports, UserForms, or Visual Basic Forms.
Much of the power in an Office document comes from the fact that users can interact with the data the document displays. This allows users to get the information that they think is important, not just the information that the document's author thinks is important.
When users view a Web page that contains an Office Web Component, they can interact with the data displayed in that document right in 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 using any language capable of working with COM objects, such as VBA, VBScript, JScript, Java, Visual C++®, and more. This lets developers create interactive content for Web-based solutions.
These controls were developed by some of the same developers who created Excel and Access, and are designed to look and feel like small versions of the Office applications from which they are derived. Although they don't have all the features found in Excel and Access, they do expose many of the commonly used features found in those applications, especially the features designed to interact with existing content.
The Spreadsheet control lets you add the functionality of a worksheet to a Web page. You can also use a hidden instance of this control on a page as a powerful recalculation engine that can work with other visible controls on a page. Think of this control as a way to take the power behind an Excel worksheet and transfer it to a Web page. You can use this control to change values, recalculate, sort, filter, scroll, protect cells, and even load data back into Excel 2000 for further manipulation.
The Chart control lets you create a two-dimensional graphical representation of data displayed in a Web page. The Chart control can be bound to any of the other Office Web Component controls, an Active Data Objects (ADO) recordset, or any ActiveX control that supports data binding. You can bind the chart to a local data source (data stored in the HTML code in the page itself) or to a remote data source (data stored in a Microsoft Access or SQL Server database, for example). As data changes in the data source, the Chart control automatically updates, scales, and sizes itself appropriately.
The PivotTable control lets users analyze data displayed on a Web page. This control combines the list features of Excel (sort, AutoFilter, and outline) with the auto-summarizing features of PivotTable reports into a single ActiveX control that runs in Internet Explorer. Therefore, a PivotTable control on a Web page is roughly equivalent to a PivotTable report in an Excel worksheet.
The PivotTable control lets users easily transform their view of data by using the mouse or simple keyboard commands. Although the author of the data determines the initial view of the data when the page is first viewed in Internet Explorer, users are free to use the dynamic run-time features of the control to manipulate and analyze the data in any way they choose. Once users have customized the data in the control, they can also save that view of the data and share it with others.
The DataSource control is best understood as the reporting engine behind data access pages, PivotTable controls, and data-bound Chart controls. The control has no run-time visual representation. It is designed to manage the connection to an underlying data source and deliver records to be displayed by other controls on a Web page. The DataSource control relies on ADO for connections to relational data sources, such as Microsoft Access or Microsoft SQL Server.
Using the Office Web Components
Okay, I've told you a bit about what these controls are and what you can do with them. I haven't given you the gory details about how you might actually get these controls onto a Web page and do something interesting with them.
Let's say you already have data and formulas in an Excel spreadsheet that you want to share with others on your company intranet. Let's say you even have some Excel charts linked to that data. And you also have an Excel PivotTable that you know others could use to analyze the data in the spreadsheet. How do you get all that cool Excel functionality onto a Web page so others can use it as well? All you have to do (here are the gory details) is make a few mouse clicks.
To create a Web page from an Excel workbook that uses Office Web Component, you simply choose the Save As Web Page command from the File menu and click the Publish button in the Save As dialog. When the Publish as Web Page dialog box appears, you use the Add Interactivity check box to enable the use of Web Components, and you choose the Web Component control you want to use from the associated combo box. When you click the Publish button, your Excel data, chart, or PivotTable is saved as a Web page at the location you specify.
That's really all there is to it. You can add script to the page to programmatically work with the controls on the page, but that discussion is for another column.
Data Access Pages
Data access pages are HTML documents comprised of HTML code, HTML intrinsic controls, and ActiveX controls. Data access pages rely on DHTML, and are designed to work best with Internet Explorer version 5 or later.
A data access page can be a simple HTML document or can include data-bound controls that let users use a Web browser to interact with data stored in a database. Microsoft Access provides a WYSIWYG design environment for creating data access pages and a means for deploying those pages and any necessary supporting files to a Web server, network server, or local file system. In addition, you can view and use data access pages within Access itself.
Data access pages support much of the functionality you are used to in Access forms and reports, but they also provide a completely new way to interact with data from within an Access database or on the Web. These objects allow users to work with data in an interactive manner through the browser in a way that has never been possible before. You can use data access pages to view, edit, or delete existing records and to add new records to an underlying record source. You can also sort and filter records as well as group records according to criteria you specify. In addition, while a page is displayed, you can manipulate and change how the records are displayed.
You can create data access pages from scratch in Access, or you can base them on existing HTML pages created by using some other HTML authoring tool. Unlike other objects in an Access database, data access pages are stored on disk as .htm files that are separate from the Access database in which they are created.
Office developers can further customize a data access page by adding scripts using the Microsoft Script Editor, which is also integrated into Microsoft Access.
Office Server Extensions
The Office Server Extensions let you create threaded discussions on any Web page. The discussion will appear whenever the page is viewed in Internet Explorer. Threaded discussions are comments added to the page or to an element on the page that can be seen and responded to by others who share the same discussion server.
You can add a discussion server, specify what information will be displayed for a discussion, or subscribe to a particular Web page, Office document on a Web server, or folder on a Web server. Subscribing to an item lets you specify when and how you will be notified of changes to the item.
You use the Microsoft Office Server Extensions 1.0 object library to programmatically work with discussion servers or discussions on a page. The Office Server Extensions object library contains objects, methods, and properties you can use to create and work with discussions on a page.
Where to Get More Info on Office Web Stuff?
You can use the following links to get more information on working with Office Web Stuff:
Office Web Components:
For more information on the Office Web Components, keep an eye on the Office Developer Hot Topics page at http://msdn.microsoft.com/office, where the Office team will feature information from an upcoming Microsoft Press book that tells you everything you ever wanted to know about these controls.
Data Access Pages:
Office Server Extensions:
David Shank is a Programmer/Writer on the Office team specializing in developer documentation. Rumor has it he lives high in the mountains to the east of Redmond and is one of the few native Northwesterners still living in the Northwest.