By Mike Gilbert
Just when you think you understand what software is all about, somebody changes all the rules. Well, maybe not all the rules, but the latest version of Microsoft Office has enough new and interesting features to make even the most experienced Office developer take notice. Microsoft Office 2000 represents a big evolutionary step in the history of desktop productivity suites, and offers the developer a host of new solution building blocks.
In this article, I'll highlight some of the new key features that you'll want to start thinking about in the months to come. Keep in mind, however, that this article was based on an early version of the Office 2000 beta, so some features may look and work differently in the final product.
Office 2000 Design Goals
One of the best places to start discussing any new product is with its design goals. These goals help you understand the genesis of new features and how they all fit together. For the sake of this discussion, I'll consider two distinct sets of design goals: those for Office 2000 itself, and those for Office 2000 programmability features, i.e. the ones that will most directly impact how you use Office 2000 to create custom solutions.
There were three primary design goals for Office 2000:
Reduce the total cost of ownership for organizations by providing more flexible and robust technologies for rolling out, configuring, and supporting Microsoft Office on tens of thousands of corporate desktops.
Increase the integration of Office with Internet technologies through better HTML editing capabilities, direct load and save from Web servers, and new integrated features for personal Web productivity. FIGURE 1 shows a simple example of this: the ability to save documents and files directly to Web servers using HTTP.
FIGURE 1: With Office 2000, you can save and edit documents on Web servers directly, using HTTP.
Make Office easier to use by providing better online assistance, improved menu systems, and smarter IntelliSense technology.
These features will benefit the Office developer, because they contribute to Office's greatest strengths as a development platform: its presence on millions of desktops around the world. The easier Office is to deploy, support, and use, the more copies of Office will be installed and accepted by end-users — and the easier it will be to deploy custom solutions based on Office.
In addition to the overall design goals for Office 2000, there were three goals for adding new developer-oriented features:
Provide greater access to Office functionality through enhanced object and event models and a unified add-in architecture based on Component Object Model (COM).
Support tighter integration of corporate data through direct OLE DB connectivity and better ad hoc analysis tools.
Make it easier for developers to create interactive, data-aware Web solutions that target the corporate intranet using the same familiar Office design tools and components.
I'll examine many of Office's new features that support these design goals in the remainder of this article. However, I should point out that in addition to the building blocks in Office 2000, Microsoft is also updating its premium edition of Office aimed specifically at the professional developer. Concurrent with the release of Office 2000, Microsoft plans to release a new version of the Office Developer Edition (ODE). The ODE features additional tools, documentation, and samples that make developing Office-based solutions or using Office components easy. In upcoming articles, I'll provide a look at some of the features in the next version of the ODE.
Backward Compatibility
Before delving into the wealth of new features, it makes sense to discuss the migration of existing solutions. Backward compatibility of custom solutions was a key development goal and drove decisions on new functionality to a great extent. Compatibility goals can be summarized as follows:
Ensure existing Office 97 solutions run under Office 2000 without modification.
Retain existing formats for Word, Excel, and PowerPoint binary files.
Ensure all existing solution components (VBA code, ActiveX controls, etc.), can be stored and retrieved from HTML documents.
With these goals met, Microsoft has provided a wealth of new opportunities while protecting your existing investment.
Better Access to Office
The foundation for any solution that involves Microsoft Office is the Office applications themselves, as well as the programmatic objects that make them up. In an effort to bring the power of Office closer to the professional developer, Microsoft has added a number of features to Office and exposed those features to developers through the COM and VBA.
One of the first things you'll notice about Office as a developer is a greater number of application events. Events are the keys to providing a rich, interactive experience to end-users. Microsoft has beefed up the event support in Word and PowerPoint to be more on par with Excel. Furthermore, events have been raised to the application level, making it easier to write centralized event processing code that deals with events on any document. For example, Word now supports WindowSelectionChange and WindowBeforeRightClick events (to name two) that propagate all document events to the top application level. Similar events have been added to Excel and PowerPoint.
In addition to events, Microsoft has added new members to the growing list of objects that you can program against. These include new objects for better programmatic control of accessibility features, a critical factor for companies developing software to support users with vision or hearing impairments.
Furthermore, with a greater emphasis on the Web and HTML publishing, new objects have been added to Word that enable developers to programmatically control HTML content, such as the HTML itself, hyperlinks, and embedded script. In general, whenever features are added to an Office application, you can be sure that there will be new objects, properties, methods and events to support them.
The final way you can gain better access to Office functionality is through a new, unified add-in architecture based on COM. An add-in (e.g. a wizard that helps build a meeting agenda) is a great way to add functionality to an application. If you tried to create add-ins in Office 97, you know the challenges. Each application's add-in architecture was different, using different document types (.DOT, .XLA., .PPA and .MDA files, for instance), loading schemes, and capabilities. The new COM-based architecture — which supplements but does not replace the existing mechanisms — means you can now create one type of add-in, regardless of the host application. Furthermore, it's also possible to create a single add-in that works in more than one application.
The add-in architecture is based on the same extensibility interface, IDTExtensibility2, used by Visual Basic 6. By implementing this interface in a COM DLL or EXE, created using any tool that can produce COM components, your add-in is connected to the host application. When your add-in loads, either when the application starts or on demand of the end-user, the add-in receives a pointer to the application's top-level object. It can then access other objects, properties, and methods to do its work. It can even tap into application events to provide an extremely high level of interactivity. This new add-in architecture promises to increase the number of add-ins written to work with Office applications. (The architecture will be supported by all Office 2000 applications, including Outlook 2000).
Better Access to Data
In addition to better access to Office functionality, Office 2000 provides better access to data. Probably the most visible and relevant manifestation of this feature is the improved integration of Access and Microsoft SQL Server.
With Office 2000, Microsoft has created a new kind of Access database project that uses SQL Server as the data store rather than a Jet .MDB file. This new format, called an .ADP file, also uses OLE DB rather than Jet to connect to SQL Server, so it's optimized for query speed. The .ADP file is a binary file that contains the connection information plus all form, report, macro, and module definitions. You can still create .MDB files for single-user or small workgroup solutions if you want, but for large-scale database projects, the .ADP file format is preferable.
In addition to using SQL Server as its native data storage mechanism, Access also lets you create, edit, and use SQL Server objects directly from the Access user interface. FIGURE 2 shows a view of the new Access 2000 user interface. You can see that Microsoft has redesigned the database container to be more like Microsoft Outlook. Plus, when you're working with an .ADP file, you'll be able to view SQL Server tables, views, database diagrams, and stored procedures in the database container window.
FIGURE 2 also shows the design window for SQL Server-stored procedures, which features color-coding of Transact-SQL syntax. This is just one of the many Visual Database tools that Access shares with Microsoft Visual Studio 6. If you use Visual Studio and Access, you should feel right at home with these tools.
FIGURE 2: The revised Access user interface shows off SQL Server objects.
Of course, the ability to edit SQL Server objects is of little benefit if you can't use them in a way that makes sense. That's why Access 2000 has the ability to use SQL Server tables, views, and stored procedures as the record sources for forms and reports. You get all the benefits of using these Access objects (ease of design, speed of development, high fidelity output) with the speed and power of SQL Server. It's a giant leap forward for the rapid prototyping and development of client/server database applications. And what about existing applications? Office 2000 will include an updated version of the Access Upsizing Tools to help you migrate existing Jet databases to the new .ADP format.
Rounding out the strong Access/SQL Server integration are better data access features in the rest of Office. One area that received particular attention was OnLine Analytical Processing (OLAP) applications, which perform sophisticated summaries and ad hoc analysis of large amounts of detailed data to help people make better decisions.
The classic example of this type of application in Office involves the use of Excel PivotTables. PivotTables enable users to summarize detailed data and look at it in a variety of ways as it relates to other data elements. In prior versions of Excel, the raw data for the analysis was stored locally, limiting the amount of data that could be analyzed. In Excel 2000, you can now create PivotTables based on a new breed of OLAP database servers, such as the OLAP extensions to SQL Server 7. In this scenario, only the PivotTable definition is stored locally. A request for a particular "slice" of the data is made to the server where the brute force processing is done. This feature paves the way for Excel-based analytical applications of unprecedented size and scope.
Supplementing data access through OLE DB and OLAP are Excel Web Queries, a little known feature of Excel 97 that has been enhanced for Excel 2000. Web Queries parse data from any Web page and place it into a range on an Excel worksheet. FIGURE 3 shows an Excel Web Query based on a page from the Microsoft Investor Web site.
In addition to a specific URL, you can also interject criteria into the Web Query. For example, the Web Query shown in FIGURE 3 takes a stock symbol from a worksheet cell and passes it to the Investor site, returning updated results to Excel. (You can even trigger a refresh when the cell value changes.) With so much information now available on the Internet, Excel Web Queries make it easy to integrate that data into a custom solution.
FIGURE 3: Excel Web Queries draw data directly from the Web.
Better Access to the Web
Speaking of the Web, Microsoft Office 2000 has a number of features designed to help developers easily create their own Web solutions that integrate live data and analytical components. One challenge that developers have faced is how to deliver data quickly and easily to users so that they can browse and, if necessary, update the data. Microsoft Access answers this challenge with a new technology called Data Access Pages (or data pages for short).
Data pages are Web pages based on Microsoft's Dynamic HTML standard that include direct links to server data using the client-side data binding technology in Internet Explorer. The Access data page designer makes creating these pages extremely easy. FIGURE 4 shows a data page in design view. You can use many of the familiar Access design tools, such as a field list that supports drag-and-drop control creation, as well as grouping, sorting, formatting, and alignment tools.
FIGURE 4: Designing a Data Access Page is very similar to designing an Access form or report.
In fact, I like to think of data pages as a cross between Access forms and reports designed for the Web. You get live, dynamic access to data, as you do with Access forms, plus you can create banded reports in HTML. FIGURE 5 shows the same data page running in Internet Explorer. The record navigation control provides the link to server data. You can use it to move from record to record, sort, filter, and update data. You can also preview the page directly in Access as you design it, the same way as forms and reports.
Beyond simple browsing, you can also create complex data pages that display related data in collapsible sections. For instance, you could create an additional section on the data page shown in FIGURE 5 that listed the products associated with each category. Using a combination of ActiveX controls and Dynamic HTML, Access would create controls that could be displayed or hidden using a collapsible outline metaphor. This powerful capability enables users to drill down into summarized information to get at the detailed data.
FIGURE 5: Data pages display directly in the browser.
Unlike normal Access objects (but similar to SQL Server objects), data pages aren't stored in the .MDB or .ADP file; only references to them are stored. Data pages are external HTML files that can be stored locally, on a network file server, or — more likely — on a Web server. As long as you have authoring rights, you can edit data pages on Web servers directly.
Data pages offer an incredible level of flexibility to developers. You can use the data-specific design tools in Access to create an overall framework for a page, but that's just the beginning. Because the end product is an HTML page, you can also integrate other elements and use additional tools. For example, you could add your own ActiveX controls, hyperlinks, streaming video, or anything else supported by HTML.
One example of extending data pages is the use of another new Office 2000 feature: Office Web Components. For years, developers have asked Microsoft for ways to use Office functionality and present Office data using small, lightweight components. Office Web Components are Microsoft's answer to this long-standing request.
Office Web Components are ActiveX controls that encapsulate discrete bits of Office functionality. Their original purpose was to provide interactive data reporting and analysis for Web pages. FIGURE 6 shows two of these components, a spreadsheet and chart, embedded on a data page.
FIGURE 6: Office Web Components bring interactive data analysis using Office tools to the Web.
One of the great things about these components is that they are data-aware. This means you can link them to any number of data sources on a Web page, e.g. data from a database, a range of cells on a spreadsheet, even arbitrary HTML elements. For example, the spreadsheet component (shown in FIGURE 6), is linked to the TotalSales field on the data page so that as the user scrolls through each record, the spreadsheet data is updated automatically. This is accomplished by creating a formula in one spreadsheet cell that references the TotalSales TextBox control via the Dynamic HTML object model (the exact formula is =document.totalsales.value). This ability to reference an arbitrary HTML element inside a formula opens a huge number of opportunities for integrating these components into existing Web pages.
Of course, because the Office Web Components are ActiveX controls, you can also use them wherever you use other controls, such as part of an Office or Visual Basic application. Each component has a robust object and event model, so you can program them like any other ActiveX control. FIGURE 7 shows the spreadsheet component embedded on a Visual Basic form. The formula bar is created using a standard Visual Basic TextBox control that is updated whenever the active cell changes.
FIGURE 7: You can use Office Web Components as part of Office or Visual Basic solutions.
FIGURE 7 also shows the spreadsheet's Property toolbox. Each component has a self-contained property dialog box (such as the one shown) that enables users to set properties without having to write any code at all.
To use the Office Web Components as part of a solution, your end-users will require an Office 2000 license. They will not, however, need to have a copy of the Office 2000 applications installed. This makes the components a great way to begin rolling out Office 2000 functionality (using the browser as a delivery mechanism), before rolling out Office itself.
Finally, to round out Office 2000's Web capabilities, Microsoft has provided an integrated development environment for client-side Web programmability. The Microsoft Script Editor, shown in FIGURE 8, is based on the popular Microsoft Visual InterDev development environment and enables developers to easily add script to any Web page created by an Office application.
While Visual Basic for Applications remains the standard technology for application programmability, the Microsoft Script Editor lets you leverage your VBA skills in creating dynamic Web pages. Microsoft has added features to the development environment to help make the transition from Office object models to the Dynamic HTML object model. For example, FIGURE 8 shows the editor displaying a drop-down list of property and method names for the object being referenced in code. VBA developers will find these IntelliSense features helpful as they work with Web page objects.
FIGURE 8: Office 2000 includes the Microsoft Script Editor for adding client-side programmability to your Web pages.
There's Much More to Come
This article represents only the tip of the Office 2000 iceberg. In coming months, you'll see more and more articles describing specific Office 2000 features and how to integrate them into your custom solutions. Office 2000 represents a major upgrade for corporate IT staffs, developers, and end-users alike. It broadens the possibilities for solution development through better access to Office functionality, tighter integration of data, and new technology for creating interactive, data-aware Web content. Plus, all of this will be tied together with a host of additional tools, documentation and samples distributed as part of the Office 2000 Developer Edition. I look forward to telling you about these features in upcoming articles.
Mike Gilbert is Technical Product Manager for Office Developer Edition and VBA Licensing at Microsoft. He is also co-author of VBA Developer's Handbook and Access 97 Developer's Handbook (with Ken Getz and Paul Litwin), both for SYBEX.