Office 2003 Application Development All-in-One Desk Reference

How to Use This Book

This book obviously can’t cover every feature in Office 2003, VBA, and espe¬cially VB.NET. Instead, as you try the many step-by-step examples in this book, you’ll become familiar with the most useful features of Office develop-ment and programming and discover many shortcuts and time-saving tricks (some that can take years to discover on your own). Believe me, some of them have taken me years to stumble upon.

Whether you want to turn a Word document into a Web site or create impressive Office 2003 solutions in Windows, this book tells you how to build what you want to build. Here are just a few of the goals that you can achieve with this book:

♦ Explore and program with new Office 2003 features such as Document Workspaces, shared attachments, OneNote, XML, and others. Some tech¬nologies explored in this book are not covered in other Office program¬ming titles, including encryption programming and the new Visual Studio Tools for Office.

♦ Build professional-looking, effective programs.

♦ See how to connect the various Office 2003 applications and data stores into a seamless, distributed, and secure business solution (and how to be smart enough to know when to use wizards to help).

♦ Make the transition from Microsoft’s traditional VBA Office language to the powerful new .NET technologies for database and other kinds of programming.

♦ Understand how to best use the many features built into VB.NET

♦ Kill bugs using powerful debugging tools.

♦ Get the most out of the Office and .NET security features, including how to automate strong programmatic encryption.

Many people think that programming is impossibly difficult and that distrib¬uted (inter-application) programming is even more difficult. It doesn’t have to be.

In fact, many common programming jobs have already been written for you in Office object libraries or the VB.NET framework, so you don’t have to do the programming at all. If you’re smart, you don’t reinvent the wheel. Sometimes, all you need to know is where in VBA to find a particular component, wizard, template, or other prebuilt solution. Then drop it into your application. This book is your guide to building efficient Office 2003 applications, utilities, and large-scale solutions.

This book tells you whether a particular wheel has already been invented. It also shows you how to save time by using or modifying existing components or Help code to fit your needs instead of building new solutions from scratch. But if you’re doing something totally original (congratulations!), this book also gives you step-by-step recipes for tackling many common tasks from the ground up.

How This Book Is Organized

The overall goal of Office 2003 Application Development All-in-One Desk Reference For Dummies is to provide an enjoyable and understandable guide for the Visual Basic programmer. This book will be accessible to developers and programmers with little or no .NET programming experience.

The book is divided into eight mini-books, with several chapters in each book. Just because the book is organized doesn’t mean that you have to be. You don’t have to read the book in sequential order from Chapter 1 to the end, just as you don’t have to read a cookbook in sequential order.

For example, if you need to add today’s most powerful encryption technology to your office solution programmatically, I suggest you read the last chapter first (Book VIII, Chapter 8).

If you want to brush up on VBA, Book II is for you. You’re not expected to know what’s in Book I to get results in Book II. Similarly, within each chapter, you can often scan the headings and jump right to the section covering the task that you want to accomplish. There is no need to read each chapter from start to finish. I’ve been careful to make all the examples as self-contained as possible. And each of them works, too. They’ve been thor¬oughly tested.

Book I: Office 2003 Essentials

This first mini-book introduces Office 2003 — explaining its purposes, what’s new in this edition, and Office’s fundamental nature. You see how common tasks are accomplished, and you discover the elements of Office program¬ming. You are introduced to the main new features in Office 2003 such as OneNote, XML, task panes, the major overhaul of Outlook, and so on. Topics in this mini-book include managing menus and toolbars, how to find program¬ming help online, understanding macro security, introduction to document workspaces, and joining the XML revolution.

Book II: Understanding Office Programming

Book II covers the primary elements of VBA. It’s a refresher course for pro¬grammers to need to brush up on classic Visual Basic programming, and a full-on programming course for people new to programming VBA, the classic language built into Office applications. All the essentials are covered, from simple concepts such as data types to advanced subjects like various secu¬rity measures that you can take to protect databases. This mini-book covers how to move Office documents and other elements to the Internet. You also see how to exploit the famous Visual Basic debugging tools.

Book III: Maximizing Word

Book III focuses on the world’s greatest word processor. You see how to work with the Word object model to tap into the power of this huge dedi¬cated language. You explore enums, ranges, selections, and the dialog object, among other topics. Then on to power editing — ways to maximize Word’s editing features. Many (perhaps most) Office workers don’t take advantage of Word’s many powerful editing capabilities. You also see how to maneuver efficiently, use Smart Documents, import data, and manage mail merge.

You explore how XML and Word now work together synergistically to facili¬tate communication between any and all platforms, operating systems, data stores, applications, and whatever else might want to communicate with Word. You see how Word does a serviceable job for smaller Internet jobs, such as displaying your pictures or blogging your feelings for all to see. You find out how to transform DOC files into Web pages. This book concludes with power macro programming: how to contact and manipulate other Office applications from within Word, how to access and modify the behavior of Word’s built-in features such as FileSave, and a set of what I consider the best Word macros available.

Book IV: Making the Most of Excel

This mini-book focuses on many aspects of programming Excel, beginning with an exploration of the Excel object hierarchy including all the expected classes, plus collection objects, ranges, charts, pivot tables, shapes, and so on. Concrete examples illustrate how you can get down deep into Excel and make it really glide across the ice like a champion skater. You also see how to respond programmatically to Excel events, automate data and XML importation, create datasets, and programmatically build pivot tables. You see how to manage goal seeking, scenarios, and summary reports and also explore problems with the Solver. You contact other Office applications from within Excel, employ UserForms, add macros to worksheet controls, automate formatting, add controls programmatically, trap keypresses, send workbooks via e-mail, and tell the differences between the activate and select methods. Whew! If I’ve left out anything you’re interested in, send me an e-mail, and I’ll include it in the next printing.

Book V: Advanced Access

There are dozens of books on Access 2003, but few I’ve found make a con¬scious attempt to integrate Access with the other Office applications. Access, poor darling, has always stood alone. It’s always been the strange stepchild — the one that doesn’t quite get into the act or the one off in the shadows in the family pictures. Access differs in many ways from the other Office 2003 applications, from its lack of direct keyboard modification to the peculiarities of its object model. Throughout this book, I’ve often found myself writing “. . . but of course, Access does this differently. Here’s how to get Access to accomplish this task.”

So I’ve done my best to always include Access in any important discussion all through the entire book. In this mini-book, though, I focus directly on Access. You see how to sort out the various database technologies and ODBC and how to move beyond VBA and DAO to ADO. You wrestle with the concurrency problem and benefit from various RAD efficiencies. Cutting-edge technologies are explored, including loading an Access database into .NET; data views; the XML Designer and XML dataset; loading XML into Access; using the new Access 2003 Developer Extensions; exploring the Package Wizard and Custom Startup Wizard; learning about the Property Scanner add-in; Smart Tags in Access; connecting to Access via automation; automating the Access runtime; using the new sandbox mode; and other topics that might interest you.

Book VI: Exploiting Outlook

No Office 2003 application has been as overhauled as Outlook. In this mini-book, you explore the new pane and other topics such as filters, spam block¬ing, encryption, special folders, and double calendars. As a programmer, you want to read the sections that show you how to exploit the Outlook object model, deal with namespaces, use MAPI objects, trap events, handle Contacts, send data between Outlook and Word or Access, create new folders, modify collections, search tasks, and manage the Outlook Calendar. Also covered are topics such as effective automatic routing (during your vacation), man¬aging multiple accounts, using send/receive Groups, blocking virii, working with profiles, sharing schedules, planning meetings, searching e-mail, and ergonomics for your users.

Book VII: InterOffice: Working as a Team

This mini-book takes a closer look at ways to integrate workers and applica¬tions to improve overall workplace efficiency. I start with OneNote, the cool new utility and notes organizer that some people cannot live without. You also see how to work well with others. It’s not always easy to avoid stepping on people’s toes when several people try to edit the same document or plan the same project. You see how to best use Office 2003 to manage shared Contacts, handle document collaboration, set up a meeting workspace and permissions, use the new Information Rights Management, change work¬space options, protect documents in Word, specify editing and formatting restrictions, create custom views, and deal with the version problem using Word’s new versions feature. You also explore topics such as building Web pages, adjusting properties, viewing code, writing scripts, doing scripting in Excel, debugging script, using forms, and sharing information efficiently. InfoPath offers a variety of useful collaborative tools. You discover designing with InfoPath, viewing data hierarchies, generating InfoPath forms from XML, and building InfoPath forms from databases.

You also see how Smart Tags can be added to your Office 2003 projects to assist users in filling out forms, getting context-sensitive help, and other ben¬efits. You see how to create, program, and test Smart Tags. You move on to the containers of Smart Tags — Smart Documents — and read about feeding data to Web sites, managing security issues, simplifying deployment, work¬ing with the elements of Smart Documents, using XML, attaching schemas, attaching the XML Expansion Pack, coding, and modifying a template.

Project 2003 isn’t ignored. You explore creating and editing projects, dealing with dependencies, understanding Gantt charts, and employing Outlook fea¬tures in your projects. Then you move on to SharePoint, beginning with the reasons why you might choose it over other collaboration technologies. You see how to install, specify permissions, use the Task Pane, manage SharePoint scalability, integrate SharePoint with office 2003 applications, and a bit about the ASP.NET connection.

Book VIII: Power Techniques: Advanced Office Automation, VBA, and .NET

If you’re looking for real heavy-duty programmer info and industrial-strength development, many of those topics are gathered together in this mini-book. But don’t be misled: Some seriously advanced topics are covered in other mini-books as well. It’s just that I chose this last mini-book to focus on some of the more cutting-edge or sophisticated techniques.

This mini-book starts off with a discussion of the drawbacks of OOP pro¬gramming and also a comparison of the qualities of VBA versus VB.NET (when you should choose one over the other). You also see code that introduces a cool .NET feature called streaming. You then create your own add-in — one of several techniques whereby you add the power of .NET to your Office 2003 programming.

Chapter 2 is all about XML and associated technologies such as XSD, XML data types, schemas, and XML programming. You wallow in objects in Chapter 3: discovering techniques for using objects in VBA, understanding .NET data types, making declarations and using events in VBA, and managing collections and arrays of objects. Then you move on in the next chapter to some advanced Internet programming topics, including working with Web Services and how XML and Office work with this interesting Internet technology.

Chapter 5 is a dive into .NET — something every serious programmer must master sooner or later. Sure, it’s a learning curve at first; Visual Basic will never be the same again, after VB.NET But believe me, what you spend in time mastering .NET, you gain in considerable additional programming capa¬bility. You see how to use software services, Internet initiatives, .NET data¬base technologies, and general programming practices. This chapter is for those readers who understand that the migration from VBA to .NET is essen¬tial (unless they’re near retirement and don’t have to worry about the future of their career).

Chapter 6 continues this migration topic by focusing on Visual Studio Tools for Office. It sounds like just the ticket. (Visual Studio is the set of utilities, editors, and languages that collectively contain .NET) It might sound like the ticket, but at this point, it’s maybe a little too unfinished to be of much real use to programmers. It has a little two-page wizard that merely sets up a template that you can use to build an Excel or Word document, using some code-behind features: that is, programming in .NET that can be used when a user opens these documents (thereby also running Excel or Word).

Read Chapter 6 to see the struggles I faced trying to get VSTO to work. Maybe it has been improved by the time you read this book, or maybe the days I spent trying and failing to get it working correctly were a result of temporary confusion on my part. Whatever. I got it mostly working — right up to the final step. So perhaps you’ll succeed where I failed. (Some on the VSTO newsgroup seem to have it working.) One other point, though: Even if it works, there are other ways to do what VSTO does. Thus, unless I’m miss¬ing something, I actually don’t understand VSTO’s raison d’être. I might not have conquered VSTO, but I do know French. Chapters 7 and 8 move you into an area of computing that is of increasing interest to all of us who program or simply use computers — security. Chapter 7 walks you through the various ways you can tighten Office 2003 security. You read about IRM, virus protection, file- and folder-based sys¬tems, macro security, signing, and hashing.

Chapter 8 is my personal favorite because to me, encryption is one of the most compelling aspects of programming. There’s something intriguing about the contest of intellects on either side — those cooking up new attacks versus those thinking up new defenses. And the computer brings an entirely new dimension to this ancient spy-versus-spy game.

For example, computers can try millions of passwords in less than an hour. This speed wasn’t possible before computerization. It’s called a brute force attack. This attack is countered by brute force encryption systems, as you’ll see in Chapter 8. When you finish this chapter, you’ll be able to employ today’s strongest encryption systems in your own programming. It’s quite a bit of power for just a little extra work.

You also discover how to harness the DES system, used today by most banks and other commercial institutions to secure their data and the messages that they send over the Internet. But you also see how to add public key encryption (RSA) to your programming. RSA is today’s most powerful encryption system, used by the military and others to transmit shorter pieces of data, such as passwords and keys. RSA isn’t generally used for actual messages (because they’re too lengthy), and although it’s fantastically secure, it’s really too slow to practically encrypt large amounts of data. But combine the two technologies, and you’ll have today’s most powerful encryption system at your disposal. Use RSA to exchange passwords or keys and then use fast DES to exchange messages.

This chapter also shows you how to avoid storing your messages on a hard drive (where, even if “deleted,” they can be recovered by widely available utilities). Instead, you see how to employ .NET streaming technologies to keep your information floating in the air like smoke — then disappearing without a trace into the encryption. These memorystreams and crypto-streams have lovely, poetic names, but they embody important, potent technology . . . technology that you’ll want to understand.

Read Comments To Download