Microsoft Office 2000 Developer

Microsoft has been refining Office as a development platform for many years, and Microsoft Office 2000 Developer (MOD) is a culmination of the company's efforts. If you want to write code with Office 2000 as your target platform, MOD provides all the tools you need: COM AddInDesigners, data-bound controls, the Package and Deployment Wizard, Visual SourceSafe (VSS) integration with Visual Basic for Applications (VBA), and the Code Librarian. Table 1, page 122, lists MOD's significant enhancements.

In MOD, Microsoft expands Office applications' object models. More important, MOD documents these object models well. To optimally use MOD, you must know applications' object models. With this knowledge, you can choose whether to use an Office facility or write code to gain functionality. For example, you can write a routine that counts the words in a Microsoft Word document or you can use Word's inherent Word Count feature. You can find MOD's object model documentation at http://msdn.microsoft.com/library/ officedev/odeomg/deovrobjectmodelguide.htm.

Office 97 developers can write VBA and ActiveX controls to extend Office's functionality, but the process is difficult. Each application has different add-in models, so you have to write native extensions specifically for each application or write VBA code for each application. MOD lets you write one add-in that works in all Office 2000 applications.

Look Familiar?
Many of MOD's tools are part of the standard Office 2000 package and other Microsoft products. However, these tools acquire new significance in MOD because Microsoft has integrated all the resources from its development products into one package.

For example, Microsoft has integrated Access 2000's database diagramming tools into MOD. You can use the Access 2000 database diagramming tool by selecting a database's Database Diagrams option in MOD. This option provides a default Relationships diagram that shows the database's entity-relationship model. MOD lets you share this diagram with Microsoft SQL Server 7.0 and Visual Studio (VS) 6.0 so that you can create tables. If you lack the proper rights to create tables, you can pass diagrams to a DBA who can create tables.

MOD also includes Office 2000's Microsoft Database Engine (MSDE), which is an alternative database engine for Access 2000. Think of MSDE as a runtime version of SQL Server. Because MSDE is SQL Server-compatible, you can develop Access databases that port to SQL Server with little or no modification. The Jet 4.0 engine is the default Access 2000 engine, so developers can still distribute an Access runtime program as well as distribute MSDE with their applications. Unlike conventional SQL Server, MSDE runs on Windows 9x.

An Access Project file, which has an .adp extension, represents Access databases stored in SQL Server or MSDE. This file contains the Access databases' code, forms, reports, macros, modules, and HTML-based database objects, and information that tells the system to use the OLE DB connector to connect to Access through SQL Server.

MSDE looks like SQL Server to users and other programs, but it lacks many of SQL Server's high-end management and development tools, such as Enterprise Manager. MSDE includes the Service Manager, which lets you stop and restart the service; network configuration tools, which ensure that the MSDE server uses the proper network protocols; and the Data Transformation Services (DTS) wizard, which shows you how to import and export data. MSDE limits database size to 2GB and performs adequately with as many as five concurrent users.

COM AddIns
One of MOD's unique capabilities is that it lets you use COM AddIns to write software that works across the entire Office suite. COM AddIns are special DLLs that all Office 2000 applications can invoke. You can write these AddIns in any compiled language that can handle COM and create DLLs.

Earlier Office versions let you write code to extend the functionality of Microsoft Excel, Word, or Access, but the code is specific to each application. COM AddIns can access the Office object model, so you can write code that is generically applicable to Office applications. Through the Office object model, COM AddIns can also gain access to SQL Server or MSDE databases and perform sophisticated operations on the data.

To help you get started with COM AddIns, MOD provides AddInDesigners, which are dialog boxes that contain options that create a component shell. In an AddInDesigner dialog box, which Screen 1 shows, you can specify identification information, the application you're targeting, and specific application versions (e.g., Word 9.0). You can customize load behavior by selecting from the following options: None, Load on demand, Startup, and Load at next startup only. You can specify the AddIn's DLL name and location. In addition, you can specify a Registry key from which the AddIn can retrieve external data.

By default, MOD writes AddIns in the VBA environment, but Microsoft provides templates for you to write AddIns in Visual C++ (VC++), Visual Basic (VB), and Visual J++ (VJ++). These templates aren't installed by default, so you must go to the \odetools\v9\samples\unsupported\mkaddin directory to install them.

Data-Bound Controls
MOD comes with several new data-bound controls, such as bound lists and bound data grids. To bind the controls to data, you use MOD's Data Environment Designer (DED). The DED lets you use ADO and OLE DB to define queries and save them in a form that you can pass to other users for use in their queries.

To create a connection in a VBA environment, select Data Environment from the Insert menu. After VBA inserts a connection object into your project, right-click the object, select Properties, and fill out the Connection dialog box to connect the object to an ADO, OLE DB, or other database source. Next, choose a driver (e.g., ODBC, OLAP, Oracle, SQL Server) and enter the server and database you'll be working with. To test the connection in the same dialog box you created it in, click Test Connection. You then use ADO to command the database. To change the database's properties, select Add Command. As you change the database's properties, the DED progressively queries the database, simplifying the building process.

Data-bound controls have been around for some time, and the DED first shipped in VB 6.0. But Microsoft's inclusion of the DED in MOD represents a qualitative leap in ease-of-use for building data-aware applications. Building read/write database applications has never been this easy.

Package and Deployment Wizard
Any software deployment can be difficult, but deploying Office solutions is especially arduous. Office 2000 application deployment is complex and involves ActiveX controls and templates for numerous application types, graphics, and add-ons. MOD's Package and Deployment Wizard simplifies this process by letting you build a deployment package on 3.5" disks, CD-ROMs, a LAN drive, or an intranet location.

To create a deployment package, select Package and Deployment Wizard from the Add-Ins menu. The system presents you with three options: Package, Deploy, and Manage Scripts. The Package option lets you bundle different files into one dependency file (i.e., a file that defines the rules for installing the program from any location). The Deploy option sends the dependency file to a server, file share, or intranet location from which users can install it. The Manage Scripts option is a simple interface from which you can delete, rename, and duplicate deployment packages.

When you invoke the Package option, the system asks for the DLL that contains the component you created (i.e., the DLL you specified for the COM AddIn), then analyzes the DLL and creates a list of dependencies (i.e., a list of files it wants to include in the package). This list probably includes the contents of the DLL, OLE custom control (OCX) files you might have used to build the DLL, Help files and HTML you created, and setup.exe and other overhead files. In addition, if you used MSDE, you'll find it in the dependency list. At this point, you can opt to include the traditional Access runtime.

You then decide whether you want the wizard to create one compressed cabinet format (CAB) file or to split the file into 1.44MB 3.5" disk-sized chunks. You define the installation's title and, optionally, a postinstallation program (e.g., display a README file, launch an application). You define where the application links go in the Start menu. Next, you use generic environment names, such as $(WinSysPath) for \windows\system, to specify the deployment package files' location on the client system. (Change the default locations at your own risk.) You can mark a file (e.g., the COM AddIn you created) as a shared file so that the system establishes a counter in the Registry for the number of applications that use that file. The system won't uninstall that file until the counter hits 0. Click Finish to complete the package. The system produces a report that lists any problems that occurred during the package-creation process. To install the package, users run setup.exe from where you placed it in the wizard.

VSS Integration with VBA
VSS is a source-code revision-control tool—a necessary facility in team-development situations. When multiple developers work on a project, two or more of them might work on the same source code file with undefined and undesirable results.

VSS is a special database for source-code files. Developers must check out code from the database to use VSS. When they're finished, they check the code back in. While a user or tool (e.g., a compiler) has code checked out, no other users or tools can access the code.

First, go to MOD's AddIn Manager and instruct MOD to load the VBA Source Code Control. Next, add the project to VSS by selecting VBA Source Code Control from the Add-Ins menu, assigning a project name, and specifying which files to control. When you right-click a file in your VBA project window, you'll see the check-in and check-out options. You can also look in the Source Code Control Status window to see who has which project files open.

Code Librarian
The Code Librarian is a database of code snippets—hundreds of code bits that perform isolated actions. When you're programming, you often write routines for the same common tasks and you usually have to look up each task's details. The Code Librarian, which Screen 2 shows, lets you browse or search for the task you need (e.g., OLE automating an Excel object) and drag the code into your project. The Code Librarian comes preloaded with snippets of Office code, mostly VBA, but you can extend it to any language. The libraries are Access (i.e., .mdb) databases, so they're easy to distribute.

The Code Librarian stores snippets in a hierarchical list with attached keywords. You can search the keywords or the database's contents. You can add your snippets to the database, or you can create a database of your snippets.

The Code Commenter is also a useful tool. You usually want to comment your code in a consistent way (e.g., a particular header-comment structure for a module or function). The Code Commenter enforces these rules.

After you invoke the Code Commenter from the Add-Ins menu, you specify that the comments apply to a specific procedure, an entire module, or an entire project. You specify your name and initials, and a template file. When you click OK, the Code Commenter uses the template and applies the comments to the code in whatever scope you specified. The Code Commenter works only in VBA.

Access Workflow Designer
MOD's Access Workflow Designer for SQL Server is a separate tool for creating Access applications that implement processes with rules. Access Workflow Designer lets you implement the stages of a project in a flowchart-like design. You assign code at different steps to implement the work at that point, and you assign code on the connectors to implement the decision-making process. You can find the Access Workflow Designer at http://msdn.microsoft.com/officedev/awd.

Do Your Homework
MOD is a useful product, but the package could do a better job of integrating tools and configuring them correctly during install. For example, many of the best features, such as the Code Librarian, are on disk, but users in a VBA environment must use the AddIn Manager to make these features available. Users need to explore Office 2000's documentation and the information at Microsoft's Office Developer Web site (http://msdn.microsoft.com/officedev/default.asp) to use this product effectively.

MOD, which includes a copy of Office 2000 Premium, costs $999. Owners of other Office 2000 versions or a VBA 6.0-enabled host application can upgrade to MOD for $399. Microsoft also offers a $649 competitive MOD upgrade for users of earlier Office versions and individual Office applications. If you write programs that use Office 2000, the cost of MOD is easy to justify when you consider the increased productivity that MOD provides for the entire Office suite.