Chapter 6

Inserting and Manipulating Content

Chapter Objectives

Estimated time: 90 minutes

When you use Microsoft Office applications like Word, Excel, and PowerPoint, your most common task is to specify an area in a document and then manipulate the content. The content you insert and manipulate can be of any type, such as text, tables, charts, and shapes. The formatting you apply generally consists of font, border, and fill attributes. Using the mouse pointer or keyboard, you scroll in the document to search visually for an area that you then manipulate with menu and toolbar commands.

Using Microsoft Visual Basic for Applications, you write code to do what you did visually: locate an area in the document and manipulate the content with methods and properties in the object model. The difference with using Visual Basic code is that you can locate and manipulate content without changing what's displayed on the user's screen. You can choose to write your code so that content manipulation occurs:

This chapter tells you how to use Visual Basic code to locate a specific area in a document and create and manipulate content such as text, tables, charts, and shapes in that area. It also describes common differences between object model members that manipulate similar content types, such as text.

In Chapters 7 and 8, you'll learn how to create instances of an Office application behind the scenes and create content without having to show the applications. In Chapter 10, you'll learn how to write code that identifies for you, based on events from the keyboard and mouse, what content the user interacts with. Based on this information, you can perform actions such as updating custom toolbars and displaying custom menus and shortcut menus.

TIP
This chapter describes the most common elements of handling content in Word, Excel, and PowerPoint. It also compares the ways in which common content elements (such as text) are different in Word, Excel, and PowerPoint. If you want to quickly see the methods and properties that Word, Excel, and PowerPoint use to insert and manipulate content, use the Macro Recorder.

Chapter 1 outlines the use of the Macro Recorder. It's highly recommended as the fastest way to learn the methods and properties required to insert and manipulate content. In several cases, the examples in this chapter were generated from the Macro Recorder and then modified for style.

Also, when the Word, Excel, or PowerPoint Visual Basic Editor is active, use the online help by pressing F1 to get more information about specific methods and properties used in this chapter. If the Office Assistant is displayed, type 'Working with Range objects' in the text box in its balloon. If the Help window is displayed, type the same thing in the Answer Wizard tab's text box.

Quick Guide: Content Objects

Each Office application deals with a different type of content. Word handles text, Excel handles grids of cells containing data, PowerPoint handles shapes and text in shapes on a slide, Microsoft Access handles structured data stored in a database, and Outlook handles items stored in your mailbox. What the main object in each application is tells you how good it is at handling and manipulating a certain type of content. For example, Word's text-handling objects, methods, and properties are far more robust than Excel's, while Excel's elements are more robust for manipulating data in cells. The following table tells you which object is used to retrieve and manipulate the most common type of data in each Office application (with the exception of Access). The table also lists the simplest code that can access each object.

ApplicationContent objectContent typeCode to access object
Word Range Text 
ActiveDocument.Range
Excel Range Cells 
ActiveSheet.Range
PowerPoint Shapes, TextRange Shapes, text 
ActiveWindow.View.Slide.Shapes
ActiveWindow.View.Slide _ 
.Shapes(i).TextFrame.TextRange

where i represents the index position of the shape in the Shapes collection.
Outlook Items Mail, notes, tasks, etc. in a folder 
ActiveExplorer.Session _ 
  .GetDefaultFolder( _ 
  olFolderInbox).Items

Although Word and Excel call their main objects Range, the word means something different in each application, although both represent the application's main content type. In Word the Range object represents the text in a document, but in Excel the Range object represents the cells in the workbook.

PowerPoint has two main objects, the Shapes collection object and the TextRange object, although text can only be retrieved through the Shapes collection. PowerPoint's text object, methods, and properties are almost as robust as Word's (while Excel's text-handling methods are very simple).

Outlook represents every item as an item in an Outlook folder. Each specific item has a type, and a specific item object exists for the item type. For example, the MailItem object represents a specific mail item in your Inbox.

In Access, your data is represented in a table in a database. The RecordSet object is commonly used to retrieve data. In Chapter 8, you'll retrieve data from a database for use in creating a Word, Excel, and PowerPoint document. The code in the Quick Guide in Chapter 8 lists how to retrieve data in an Access database. You'll execute the first procedure in the Quick Guide from within an Access database, and you'll execute the second procedure from the Visual Basic Editor within another Office application.

Accessing Content In Multiple Ways

You may find as you gain experience with Visual Basic that sometimes there is more than one way to retrieve or set content, or information about content, in Word, Excel, PowerPoint, or Outlook. For example, if you start with a new workbook in Excel, the following lines set the same cell to the same value.

ActiveSheet.Cells(1, 1).Value = "Region"
ActiveSheet.Range("A1").Value = "Region"
ActiveSheet.Cells.Range("A1").Value = "Region"

In fact, because the Value property on the Range object in Excel is the default property, you really don't need to specify Value in the lines above. If you remove the .Value from each line above, the lines will still be functionally equivalent. In Word, PowerPoint, and Outlook, you'll find different lines of code that produce the same result. However, each way is slightly different—better suited for specific scenarios and for developing more compact, efficient code. Each section in this chapter describes cases in which there is more than one way to locate and manipulate content.