77-529 Developing and Implementing Solutions with Office 2000 and VBA

Courses:

77-529 Developing and Implementing Solutions with Office 2000 and VBA

click here to download the course outline

Developing and Implementing Solutions with

Office 2000 and VBA

Course Specifications

Course number: 077529

Software Version Number: 6.0

Course length: 5 day(s)

Course Description

Overview: Students will learn design considerations and deployment scenarios related to building

customer solutions using Microsoft Office 2000. Using the methodologies presented, students will be

able to build solutions that access data and automate business processes, using Microsoft Office 2000.

In addition, the content of this course is directed at assisting the student to prepare for the Microsoft

Exam 70–091.

Prerequisites: To ensure your success, we recommend you first take the following Element K course or

have equivalent knowledge:

•

Knowledge of the basic features of the Microsoft Word, Excel, PowerPoint, and Access

applications:

•

Able to record simple macros by using Macro Recorder and invoke them:

•

Possess ability to perform the following functions using Visual Basic: write a function

procedure and a sub procedure; handle an event procedure; retrieve and validate information

from a user; build a user interface with forms and controls; describe the relationship between

objects, properties, and methods; add run-time error handling to an application; and run an

application, using the debug mode in the Visual Basic environment:

•

Define a database table:

•

Define a database query:

•

Create database queries using SQL syntax:

•

Define the terms Internet, intranet, and Web browsing:

•

Possess a basic understanding of Microsoft Visual Basic and Office product functionality, or

having successfully completed an Access 2000 programming or Visual Basic 6.0 programming

course:

Delivery Method: This course is designed for instructor-led, group paced classroom-delivery, providing

students with structured hands-on activities.

Hardware/Software Requirements

•

PC with Pentium 75 MHZ or higher processor;

•

Microsoft 95, Microsoft 98, or Windows NT

•

At least 64 MB of RAM

•

252 MB for Disc 1 (Word, Excel, Outlook, PowerPoint, Access, FrontPage)

•

174 MB for Disc 2 (Publisher, Small Business Tools)

•

100 MB for Disc 3 (PhotoDraw)

•

130 MB for Microsoft Developer (minimum installation)

•

60 MB for the MSDN library (minimum installation)

Performance-Based Objectives

Lesson objectives help students become comfortable with the course, and also provide a means to

evaluate learning. Upon successful completion of this course, students will be able to:

•

Describe design considerations and deployment scenarios when building a custom solution,

using Microsoft Office 2000.

•

Identify situations when Microsoft Visual Basic for Applications code is appropriate for a

document, a document template, or a component object model (COM) add-in.

•

Use the Visual Basic Editor to write and run Visual Basic for Applications code and manage

files.

•

Customize the user interface of Office 2000 applications, using command bars and forms.

•

Access data sources and retrieve data by using ActiveX Data Objects (ADO).

•

Use the Office 2000 Applications to access data in a relational or non-relational database, and

manipulate and present the data.

•

List and describe commonly used objects in the OFfice 2000 object models.

•

Use the Microsoft Excel object model to programmatically navigate, format, present, and

analyze data.

•

Use the features in Microsoft Office 2000 Developer to build a COM add-in, use digital

signatures to enhance security, and deploy applications.

•

Use the Microsoft FrontPage 2000 Web site creation and management tool to create a Web

site and display documents created in other Office 2000 applications.

Course Content

Lesson 1: Microsoft Office 2000

Topic 1A: Designing an Office Solution

Task 1A-1: Using the Code Librarian

Topic 1B: Deploying an Office 2000 Solution

Task 1B-1: Prepare a Document for the Web

Lesson 2: Using the Visual Basic Editor

Topic 2A: Identify Solutions that Work Best with VBA

Task 2A-1: Understanding Office Applications with VBA

Topic 2B: The Visual Basic Environment

Task 2B-1: VB Editor Commands

Task 2B-2: The Event Procedure

Task 2B-3: Creating a Macro in Word

Task 2B-4: Insert Modules and Procedures into Project Explorer

Task 2B-5: Running Code from the Host Application

Topic 2C: Using Debugging Tools

Task 2C-1: Use the Debug Toolbar

Lesson 3: Using Visual Basic with Office 2000

Topic 3A: Declaring Variables with Appropriate Scope

Topic 3B: Decision Control and Looping Code

Task 3B-1: Using Visual Basic Syntax

Topic 3C: Uses of Object Models

Task 3C-1: Object Model Information in Help

Topic 3D: Office 2000 Shared Object Models

Task 3D-1: Shared Object Models

Topic 3E: Early and Late Binding of Object Variables

Task 3E-1: Using an Object Model

Topic 3F: Customizing the User Interface

Task 3F-1: Creating a Form

Task 3F-2: Adding Controls

Task 3F-3: Setting Control Properties

Task 3F-4: Setting Tab Order of Controls

Task 3F-5: Control Initialization and Validation

Task 3F-6: Recording a Macro

Task 3F-7: Automate Calling with Macro

Task 3F-8: Adding a Command to a Menu

Lesson 4: Office Solution Data Retrieval

Topic 4A: Accessing External Data Sources

Task 4A-1: Importing Data from Excel to Access

Topic 4B: Selecting the Optimal Office 2000 Applicationfor Data Storage and Manipulation

Topic 4C: Using Components of the ADO Model

Task 4C-1: Retrieve and Manipulate Data with ADO Recordset Object

Topic 4D: Structured Query Language (SQL)

Task 4D-1: Use the Access Query Wizard

Task 4D-2: Build a Query with Access and Copy its SQL Statement toan Excel ADO

Topic 4E: Handling Common Errors in ADO

Task 4E-1: Using the VBA Error Handler Add-In

Lesson 5: Building a Document in Excel

Topic 5A: Capabilities of the Excel Object Model

Task 5A-1: Reference an Excel Worksheet from PowerPoint

Topic 5B: How to Design Your Solution Using the MicrosoftExcel Object Model

Task 5B-1: Using Workbook Events to Display Messages

Task 5B-2: Programmatically Add/Rename a Worksheet to a Workbook

Task 5B-3: Using Worksheet Events to Improve a Solution

Task 5B-4: Writing Code that References Cell Ranges on a Worksheet

Task 5B-5: Programmatically Formatting/Inserting Values and Formulas

Task 5B-6: Getting Data from Other Applications

Lesson 6: Excel Data Analysis and Presentation

Topic 6A: Accessing Data Using Office 2000 Applications

Task 6A-1: Using Microsoft Query to Bring Data from an Access Databaseinto Excel

Topic 6B: Using Office 2000 Applications to ManipulateData in an Excel Pivot Table

Task 6B-1: Creating a Pivot Table Manually

Task 6B-2: Programmatically Creating a Pivot Table

Task 6B-3: Grouping and Updating

Topic 6C: Manipulating Data in an Excel Chart

Task 6C-1: Creating a Chart Manually Using Chart Wizard

Task 6C-2: Programmatically Creating a Chart

Task 6C-3: Programmatically Modifying the Chart/Style/Format

Task 6C-4: Modifying an Existing Chart

Topic 6D: Using Office 2000 Applications to Present Data

Task 6D-1: Publishing a Document as Static HTML

Task 6D-2: Publishing an Excel Worksheet as an Interactive Web Page

Task 6D-3: Introduction to the Microsoft Script Editor

Lesson 7: Microsoft Word Solutions

Topic 7A: Common Objects for Word in Office 2000 ObjectModels

Task 7A-1: Explore the Word Object Model

Topic 7B: Using VBA to Create, Save, and Open Documents

Task 7B-1: Use VBA to Create a Word Object from Excel

Task 7B-2: Build a Word Template and Attach it to a Document with VBA

Topic 7C: Programmatically Insert and Format Text in aWord Document

Task 7C-1: Setting a Range to Refer to a Paragraph and Format It

Task 7C-2: Create a Table and AutoPopulate its Cells with VBA

Task 7C-3: Creating an AutoText Entry and Inserting It at a BookmarkLocation

Topic 7D: Insert Excel Data into a Word Document Programmatically

Task 7D-1: Send Excel Data to Word with VBA

Topic 7E: Save a Document in HTML for Publication on theWorld Wide Web

Task 7E-1: Converting a Word Document to HTML

Lesson 8: Additional Office Solutions

Topic 8A: Preparing and Publishing PowerPoint Presentations

Task 8A-1: Adding and Editing Slides

Task 8A-2: Using the Macro Recorder

Topic 8B: Customizing an Office Assistant

Task 8B-1: Using the Object Browser

Task 8B-2: Creating a Balloon

Topic 8C: Access Solutions

Task 8C-1: Using an Access Database from Excel

Topic 8D: Customizing Outlook

Task 8D-1: Creating an Appointment

Task 8D-2: Error Mail for Help Desk

Lesson 9: Office 2000 Advanced Development Issues

Topic 9A: Using the Code Librarian

Task 9A-1: Creating A Code Librarian Entity

Task 9A-2: Add Code to the Code Librarian

Topic 9B: COM Add-Ins

Task 9B-1: Create a COM Add-in Project

Topic 9C: Using Digital Certificates

Task 9C-1: Create a Digital Certificate

Task 9C-2: Sign a Project

Topic 9D: Implementing an Office Solution

Task 9D-1: Using the Package and Deployment Wizard

Lesson 10: Publishing Office 2000 Solutions Using FrontPage2000

Topic 10A: Web Site Development Strategies

Topic 10B: Differentiate Between Server Extensions

Topic 10C: The FrontPage 2000 Object Model

Task 10C-1: Using FrontPage Application Events

Task 10C-2: Working in the Navigation Structure: Navigation Nodes

Task 10C-3: Working in the Navigation Structure: Returning Web Files

Task 10C-4: Inserting Text into a File

Topic 10D: Create a Web Page in FrontPage 2000

Task 10D-1: Create Basic Business Web Page

Task 10D-2: C-STARS Corporate Web Site

Topic 10E: Displaying Office 2000 Documents On A Web Site

Task 10E-1: Importing Office Documents in a FrontPage Web Site

Appendix A: Executive Summary: Original C-STARS Proposal

Introduction to C-STARS

Appendix B: Meeting Notes: C-STARS Director

Meeting Information You Noted

Appendix C: Meeting Notes: C-STARS Educational Services Manager

Meeting Information You Noted

Appendix D: Meeting Notes: C-STARS Research Manager

Meeting Information

Appendix E: Meeting Notes: C-STARS Public Relations Manager

Meeting Information You Noted

Appendix F: Visual Basic Editor Shortcuts

Search Site

77-529 Developing and Implementing Solutions with Office 2000 and VBA

Online Content

IL Training

Online Ordering

Access Our LMS

Search Courseware