Page 32 - Microsoft Office Excel 2003 Programming Inside Out

Part 1: Preliminaries
Microsoft Office Excel 2003 Programming Inside Out

Creating Smart Documents

Excel workbooks have always been able to interact with other Office documents, but those
interactions have been somewhat limited. Creating links to data on the Web, in a database, or
in another Office document are all valuable abilities, but the workbook was simply a stand-
Chapter 1
alone collection of bits. In Office 2003, smart documents are Office documents that have
metadata (that is, data that tells the document about itself) with information regarding how
they fit within a business process, allowing the documents to take programmed actions based
on that context.
As an example, if you work at a technical services firm that uses an Excel-based template to
track the time you spend on various projects, you probably create a new workbook (with one
worksheet) at the beginning of every week, save it with your identifying information, and fill
in your time at the end of each day. Then, at the end of the week, you either save the work-
book to a network folder or e-mail the workbook to your administrative contact. Smart doc­
uments, by contrast, have programming that fills in the details about how your timecard fits
in the business process. When you create a new timecard workbook, Excel recognizes who
you are and fills in your personal data (name, employee number, projects, etc.). Then, when
you’re ready to leave for the week, the smart document displays a button that lets you send
the workbook to the next stage in the process. And as far as you’re concerned, it doesn’t mat­
ter what mechanism is used to send the data along; it could be written to a database, saved as
a worksheet in a workbook elsewhere on the network, incorporated into data on a BizTalk
server tracking workflow issues, or attached to a Microsoft Outlook e-mail message and sent
to your administrative contact.

Creating Document Workspaces

An increasing number of documents require input from more than one person. When you
estimate the cost of a large project, for example, you will probably require input from every
member of your team to determine which products and components should be considered
and ultimately used in the project, not to mention the amount of labor and corporate over-
head spending required to support the project internally. Sharing and merging workbooks is
one way to get the information out there, but the process is filled with pitfalls. If one of your
colleagues adds or changes a password for their copy of the workbook, for example, you
won’t be able to include their changes in the merge.

When you use Excel 2003 in conjunction with Microsoft Windows SharePoint Services, how-
ever, you can streamline the process of collaborating, editing, and reviewing workbooks by
creating a document workspace. A document workspace is a virtual work area set up on a
SharePoint site that lets individuals with access to the workbook modify the copy on the
SharePoint server or work with a local copy, which they can update by adding changes from
the master copy in the document workspace. After you establish the document workspace
and give each of your team members access to that portion of the site, your colleagues will be
able to work on the same copy of the workbook. Any changes saved to any copy of the

6