Office XP Development with VBA

Description

• Copyright 2002
• Edition: 1st

• Book
• ISBN-10: 0-13-065417-5
• ISBN-13: 978-0-13-065417-5

• Create custom business solutions with Microsoft Office XP and Office 2000!
• Extend Word, Excel, Outlook, and other Office applications.
• Learn all aspects of Office development, including security and distribution.
• Leverage Office's powerful collaboration, intranet, and Web capabilities
• For novice developers and Office power users!
• Discover the power of Office programming
• Applies to both Office XP and Office 2000
• Master the Visual Basic for Applications (VBA) language and development environment
• Understand the powerful components in the Office object model
• Build custom tools that leverage email and the Web
• Construct four Office applications - step-by-step
• No Microsoft Office programming experience necessary!

Developing robust custom solutions with Microsoft Office XP/2000!Microsoft Office is far more than the world's leading office productivity suite. Itspowerful capabilities are organized into hundreds of programmable components, all available to any developer who needs to create custom business solutions. Bycombining these components with the powerful Visual Basic for Applications (VBA) programming language, you can construct powerful applications far more quickly than if you built them from scratch. These components and VBA are available in both Office XP and Office 2000, which means you can use them whether or not you upgraded.

In this book, expert developer and consultant Peter G. Aitken shows exactly how toleverage the power of Microsoft Office in custom application development. Whetheryou're an experienced developer or a business professional with a problem that needssolving, Aitken will show you how to do it with Office — step by step.

Aitken begins by reviewing each key Office application - Word, Excel, Access,PowerPoint, Frontpage, and Outlook - and the shared components accessible to all ofthem. You'll discover powerful opportunities for extending and automating Office to increase efficiency and improve collaboration throughout your organization. Then, onestep at a time, Aitken teaches the key VBA skills you'll need to achieve your goals. Finally, this book walks you through the construction of four real-world Office applications, emphasizing the potential value of Office custom solutions in real-world business situations.



Sample Content

Table of Contents

(NOTE: All chapters conclude with Summing Up.)

Preface.

I. INTROUDCTION TO OFFICE DEVELOPMENT.

1. Why Develop with Office VBA?

Advantages of Office Development. Software Components. Objects and Components. The Office XP Suite. What's New in Office.

2. The Basics of Office Development.

Objects and Automation. The Office Object Model. Designing Your Custom Application. A Simple Office Application.

3. Visual Basic for Applications—the Fundamentals.

Parts of the VBA Editor. Working with Code. Importing and Exporting Modules. Writing Good Code. Adding References. Securing your Code.

4. Working with Office Objects.

Objects and References. Collections. Finding Objects in the Object Hierarchy. Early versus Late Binding. The Object Browser. Objects and Events.

II. THE OFFICE COMPONENTS.

5. Using Word Components.

The Word Object Model. The Document Object. The Selection Object. The Range Object. Using Bookmarks. Finding and Replacing Text. The Application Object. The View Object. Events in Word.

6. Using Excel Components.

Excel Overview. The Excel Object Model. The Workbook Object. The Worksheet Object. Range Offsets, Unions, and Intersections. Using the Cells Property. Naming Ranges. Manipulating Worksheet Contents. Working with Charts. The Application Object. Events in Excel.

7. Using Data Access Components.

Access Fundamentals. Data Access Technologies. The Access Object Model. Working with Forms and Reports. Working with Data Access Pages. Creating Data Access Pages. Using Data Access Pages. Securing Databases. Database Passwords. Active Data Objects. RecordSets.

8. Using PowerPoint Components.

The Presentation Object. The Slide Object. Working With Text on Shapes. The PowerPoint Application Object.

9. Using FrontPage Components.

FrontPage Overview. The Web Object. Working with Folders. Working with Web Pages. Working with Web Pages. Working with Page Windows. Dynamic HTML—Should You Use It?

10. Using Outlook Components.

Outlook Overview. The Application and NameSpace Objects. Manipulating Folders and Items. Working with Mail Messages. VBA in Outlook. Events in Outlook. Programming the Calendar.

11. Using the Shared Office Components.

Shared Components Overview. The Office Assistant. Displaying Controls in a Balloon. Using Modeless Balloons. The FileDialog Object. The FileSearch Object. Command Bars. Document Properties.

III. THE VBA LANGUAGE.

12. Data Storage and Operators.

Basic VBA Syntax. Storing Program Data. Arrays. Static Arrays. Dynamic Arrays. Operators.

13. Conditional and Loop Statements.

Conditional Statements. Loop Statements. The GoTo Statement.

14. Writing Procedures.

Types of Procedures. Defining a Procedure. Passing Arguments. Variables in Procedures. Calling a Procedure. Named Arguments. Procedure Scope. Planning and Storing Procedures.

15. Working with Strings.

String-Processing Summary.

16. Working with Dates and Times.

The Date Data Type. Creating Dates and Times. Adding and Subtracting Dates and Times. Getting Date and Time Information. Formatting Dates and Times.

17. Working with Files.

Overview. File Access. Working with Text Files. File Management. FSO Methods. The Drive Object. The Folder Object. The File Object.

18. Creating Custom Classes and Objects.

Why Create Classes? Class Module Fundamentals. Creating Class Properties. Creating Class Methods. Class Events. A Class Demonstration.

19. Creating User Forms.

Creating a User Form. What Controls Are Available? User Form Code and Event Procedures. Displaying, Using, and Hiding User Forms. A User Form Demonstration.

IV. OTHER OFFICE DEVELOPMENT TOOLS.

20. VBA Error Handling.

Writing Solid Code. Runtime Errors. The Err Object. Handling an Error. Resuming Executing after an Error. On Error Resume Next. ADO Errors. Raising Errors. Class Module Errors. Returning Errors from Functions. Testing your Error-Handling Code. Error Logging. Dealing with Unanticipated Errors.

21. Debugging, Deploying, and Supporting Your Application.

Debugging Your Applications. Working with Breakpoints. Using Watches. The Locals Window. Controlling Program Executing. Deploying Your Application. Supporting an Application with Online Help.

22. Using the Windows API and Registry.

The Windows API. Strings and API Procedures. Using Callbacks. The Windows Registry.

23. Office Technologies for the Web.

Web Components Overview. Using the Web Components. Using the Spreadsheet Control. Using the PivotTable Control. Using the Chart Control. Using the DataSource Control.

V. PUTTING IT ALL TO WORK.

24. Web Publishing of a MultiAuthor Compound Document.

The Scenario. Planning. The Main Application.

25. Numeric Processing and Graphing.

The Scenario. Planning. Creating the Workbook. Planning the Code. Potential Errors and Bugs. The Main Procedure. The Secondary Procedures.

26. The Scenario. Report Generation Basics. Structure of a Report. Adding Report Elements. A Demonstration.
Appendix: KeyCode Constants and Values.
Index.

Preface

Preface

When most people think of Microsoft Office, they think of a powerful set of office applications-Word for word processing, Excel for spreadsheets, Access for databases, and so on. There is, however, much more to Office than that. Hidden behind the applications programs is a powerful set of development tools that can be used to create sophisticated custom solutions to address specific needs. The foundation of these development capabilities is the Office object model, a rich set of programmable objects, sometimes referred to as software components. These components provide the functionality of the individual applications that make up Office and are also available for customized programming. When combined with the powerful Visual Basic for Applications (VBA) programming language, also part of Office, the result is a powerful and flexible development tool that is often your best choice when faced with a Windows development project.

Is Office a replacement for traditional programming languages such as Visual Basic, C++, and Java? In a word, no. From the developer's perspective, Office is specialized for creating custom applications that perform the same general sorts of tasks that the Office applications themselves do-manipulate text, work with numbers, display graphs, send and receive e-mail, and so on. Since it is exactly this type of functionality that is often needed, there are many situations in which Office will be your best choice of development tools. Outside this area, however, Office is not a good choice. For example, an astronomer writing a program to analyze crater patterns on Mars would not turn to Office. There are, however, so many situations in which Office is the best choice that any Windows developer really should have some familiarity with its capabilities. Office may be your only development tool, or it may be one of half a dozen that you use. Regardless, it cannot be ignored.

This book is aimed at individuals who are at the beginner and intermediate levels and who want to use Office XP to develop custom solutions. No previous programming experience is required, although if you do have such experience, particularly with Visual Basic, you'll be able to work through some sections of the book more quickly, Part Three in particular. My approach is a combination of reference material and demonstrations. I am a strong believer in learning by doing, and I feel that the best approach to learning how to use a development tool is a mixture of presenting the raw information you need, and showing you how it is used in a real-world situation.

I make no pretense of covering all the details of Office development. There is no way a single book can include all the related information, and even if such a book were possible, no one would want to read it. My goal is to cover the most important fundamentals of Office—those tools and techniques that you will need most often. Once you understand these fundamentals, it is an easy matter to turn to the online reference materials for the details that could not be included in the book.

This book covers the latest version of Office, which is called Office XP. If you have programmed with the previous version, Office 2000, you'll be glad to know that, for the developer, very little has changed. Solutions that work with Office 2000 should all run in Office XP with no modifications. The few changes that have been made are all in the nature of additions to the object model.



Updates

Submit Errata

