Using VBA to Access Document Content in the 2007 Office System

Article
07/09/2014

Summary: Accomplish more with Microsoft Visual Basic for Applications (VBA) when you write macros that can access content without first selecting that content. This article looks at common VBA constructs for working with content in Microsoft Office Excel 2007, Microsoft Office PowerPoint 2007, and Microsoft Office Word 2007. (6 printed pages)

Stephanie Krieger, arouet.net, Microsoft Office MVP

March 2009

Applies to: 2007 Microsoft Office system, Microsoft Office Excel 2007, Microsoft Office PowerPoint 2007, and Microsoft Office Word 2007

Contents

Getting Right to the Good Stuff
Using Collections to Identify Objects by Name and Number
Working with Ranges
Be Specific
Additional Resources
About the Author

Getting Right to the Good Stuff

Watch the Video: Using VBA to Access Document Content in the 2007 Office System

Like many people, I initially learned Microsoft Visual Basic for Applications (VBA) by recording macros and then reading the code I recorded. Because of that, the early macros that I wrote always selected any item before acting on it.

However, when you start writing macros, you are certain to run into scenarios where having to select the object limits what you can do or at least seems to be more work than should be necessary.

For example, perhaps you need a macro that adds text to the header in only the second section of a Microsoft Office Word 2007 document. If you need to act on the selection (as a recorded macro does), you may write a macro that looks like the following code:

Sub SelectToAddHeaderText()
    Selection.GoTo What:=wdGoToSection, Count:=2
    With ActiveWindow.ActivePane.View
            If .Type = wdReadingView Then
            .Type = wdPrintView
            End If
            .SeekView = wdSeekCurrentPageHeader
                With Selection
                    .HeaderFooter.LinkToPrevious = False
                    .TypeText Text:="Some sample text."
                End With
            .SeekView = wdSeekMainDocument
    End With
End Sub

In the preceding code, you do the following:

Move your insertion point to section two.
Determine whether the active view is Full Screen Reading view (from which the header can't be accessed programmatically) and, if so, change the view to Print Layout.
Open the header.
Turn off Link To Previous so that the text you add only affects the current section header.
Add your text.
Close the header.

Some of the downsides to this process are obvious. You have to change the location of your insertion point and, if different first page or different odd and even headers are enabled, your text may be added to the wrong header.

One significant downside, however, is less obvious—this code simply doesn't work properly. The problem is that, when the macro disables Link to Previous for the selected header, your selection is changed to the preceding header automatically and the text ends up in the header for section one.

Note
If you record a macro to take the same steps as in the preceding code sample, you see additional code that you don't need but you also get the same erroneous result when you run that macro because you're acting on the selected header.

So, how do you get content into the correct header? Do you have to move your insertion point again, close and reopen the header, or take some other cumbersome workaround? Not at all. Instead, just specify the header you want to access initially and you never have to select anything, as follows:

Sub AddHeaderText()
With ActiveWindow.ActivePane.View
    If .Type = wdReadingView Then
    .Type = wdPrintView
    End If
End With
With ActiveDocument.Sections(2).Headers(wdHeaderFooterPrimary)
    .LinkToPrevious = False
    .Range.Text = "Some sample text"
End With
End Sub

In the preceding code, you do the following:

Determine whether the active view is Full Screen Reading view and, if it is, change the view to Print Layout.
Specify the exact header to which you want to add content.
Turn off link to previous for the specified header.
Add the content to that header.

You can now resume work without interruption, because your insertion point never moved.

By specifying the content that you want to act upon, you not only create less work for yourself and for your macro, you can accomplish more and reduce errors. Because the required header is specified in this code sample, turning off Link to Previous does not change where the macro inserts your text.

This article explores several common ways to access content using VBA in order to help you write more efficient and more effective macros.

Note
This article assumes that you have some basic experience writing or editing VBA macros. For help getting started or filling in blanks, see Additional Resources

Using Collections to Identify Objects by Name and Number

Consider the following statement:

ActiveDocument.Sections(2).Headers(wdHeaderFooterPrimary).Range.Text = "Sample text."

In this statement, the Sections property of the ActiveDocument object returns the Sections collection object (that is, the collection of all sections in the active document). So, you can specify a section from within the collection to act upon.

Depending upon what you need to accomplish, you can also act on the entire collection. Notice the different IntelliSense list options for the Sections collection and an individual Section object as shown in Figure 1 and Figure 2, respectively.

Figure 1: The IntelliSense list for the Sections collection object, showing some of the properties and methods available to that collection.

The IntelliSense list for the Sections collection

Figure 2: The IntelliSense list for a specified section, showing some of the properties available to an individual Section object

The IntelliSense list for a specified section

Similarly, the Headers property returns a HeaderFooter collection (in this case, all headers in the second section of the active document).

Note
When you type the open parentheses after Headers, you see the IntelliSense list of available built-in constants for the HeaderFooter types.

It may appear that some types of objects can be specified by an index number and others by name (or a constant, such as the section type). However, any object that you can specify by name or with a constant can also be specified by an index number. For example, if you copy the preceding line of code into the Visual Basic Editor (VBE), right-click the constant wdHeaderFooterPrimary and then click Definition, the Object Browser opens to the list of constants that are members of wdHeaderFooterIndex and shows you that the index number of wdHeaderFooterPrimary is 1. So, the previous statement could also have been written as follows:

ActiveDocument.Sections(2).Headers(1).Range.Text = "Sample text."

Take a look at a couple of additional examples.

To specify a worksheet in your Microsoft Office Excel 2007 workbook, you might use either the sheet name or the sheet index number as shown here (substitute the name of your worksheet for MyData in the first statement):
```
ActiveWorkbook.Sheets("MyData").Range("B2:C12").Interior.ThemeColor = msoThemeColorAccent2
ActiveWorkbook.Sheets(1).Range("B2:C12").Interior.ThemeColor = msoThemeColorAccent2
```

To specify a presentation in the collection of open Microsoft Office PowerPoint 2007 presentations, you can use either the presentation name or index number (substitute the name of your presentation for MyPres.pptx in the first statement):

Presentations("MyPres.pptx").Slides.Add Index:=2, Layout:=ppLayoutComparison
Presentations(1).Slides.Add Index:=2, Layout:=ppLayoutComparison

Note
In the Presentations collection in PowerPoint and the Workbooks collection in Excel, the collection index starts with the document that has been open the longest (so that Presentations(1) is the first of the presentations that you opened). The Documents collection in Word is the opposite—Documents(1) is the most recently opened document in the collection. In all of these cases, remember that these collections only refer to those documents, presentations, or workbooks that are currently open. Also note that you can loop through collections to act on multiple objects at once (such as all tables in a document or only those tables that meet specified conditions). To learn about using loops and conditional statements in your macros, see the article Using VBA to Format Long Documents in the 2007 Office System in a Fraction of the Time.

In the Presentations collection in PowerPoint and the Workbooks collection in Excel, the collection index starts with the document that has been open the longest (so that Presentations(1) is the first of the presentations that you opened). The Documents collection in Word is the opposite—Documents(1) is the most recently opened document in the collection. In all of these cases, remember that these collections only refer to those documents, presentations, or workbooks that are currently open.

Also note that you can loop through collections to act on multiple objects at once (such as all tables in a document or only those tables that meet specified conditions). To learn about using loops and conditional statements in your macros, see the article Using VBA to Format Long Documents in the 2007 Office System in a Fraction of the Time.

Working with Ranges

The term range is used in different ways depending on whether you're working in Word, Excel, or PowerPoint. However, it always refers to a content area.

When you use a macro to access content in a Word document, such as adding text in a header, formatting a picture, or deleting cell content, your instinct may be to act on a selection (as in the first example shown earlier in this article). However, you can often act on that object without selecting it first by using a Range object.

Take one more look at the statement for adding text to the main header in the second section of the active document:

ActiveDocument.Sections(2).Headers(wdHeaderFooterPrimary).Range.Text = "Sample text."

The Range property of the specified Header object resolves to a Range object that represents the content area of that header. By using Range objects, you can act on content in many parts of your document without ever changing the location of your insertion point (that is, your selection).
Include the Range property in a statement when you want to act on the content within a specified range. For example, the following two statements do not take the same action:
```
ActiveDocument.Bookmarks(1).Delete
ActiveDocument.Bookmarks(1).Range.Delete
```
The first of the preceding statements deletes the specified bookmark. The second statement deletes the bookmark as well as any content (such as text and objects) contained within the bookmark range.

When you work in Word, whether using a Selection object or a Range object, the Information property can be extremely handy. That property can provide information that enables you to avoid errors in your macros or take specific actions. For example, without having to change your selection, the following statement tells you the page on which section two of the active document ends:
```
ActiveDocument.Sections(2).Range.Information(wdActiveEndPageNumber)
```
See the Word Information Enumeration listing in the Word Developer Reference for a list of available information types.

PowerPoint does not use an object named Range. However, several objects are used to refer to specific types of ranges, such as SlideRange, ShapeRange, and TextRange.

ActiveWindow.Selection.SlideRange.Shapes(1).Fill.ForeColor.ObjectThemeColor = msoThemeColorAccent3
ActivePresentation.Slides(3).Shapes(2).TextFrame.TextRange.Text = "Sample text."

In the first statement, the SlideRange property returns an object that represents the current slide selection. Notice that, although this statement acts on a selection in terms of the active slide window, you can specify the shape on which you want to act without selecting it first.
In the second statement, the construct is similar to the one used earlier for adding text to a header in Word. Nothing needs to be selected because the statement is able to specify the precise location of the content on which you want to act. Note that TextFrame in this case refers to the internal text box within the specified shape and TextRange is the content range within that text box.

In Excel, the term range may be the most familiar, as the Range object in Excel VBA is used to refer to a worksheet range.

ActiveWorkbook.Sheets(2).Range("A1:B12").FormulaR1C1 = "=RANDBETWEEN(100,200)"
ActiveWorkbook.Sheets(3).Range("MyRange").FormulaR1C1 = "Sample Text"

Notice in the preceding statements that the range can be a specified cell range or a named range, just as when you work with ranges in the Excel UI.

Be Specific

As shown throughout this article, when you get more specific with your code, you can often write less code and accomplish more. In many cases, you can get even more specific than the examples provided here.

Notice that objects such as ActiveDocument, ActiveWorkbook, ActivePresentation, or ActiveWindow all refer to an active selection. That is, they require the referenced object to be the active object when the macro runs. This is often appropriate and even desirable, such as to create a macro that you can use to format all shapes on the active slide.

However, with more complex code or code that needs to run when multiple windows may be open, you can avoid errors (such as the risk that the window you need loses focus during code execution) by specifying the specific target item rather than using the active selection. For example, you may specify Documents(1) or Documents("MyReport.docx") rather than using ActiveDocument.

In fact, more experienced developers often go one step further and declare an object variable to represent the specific target object, such as in the following example:

Dim oDoc As Document
Set oDoc = Documents("MyDoc.docx")

Note
Declaring an object variable can also help save you time by providing more information than you can get without them. Some built-in property references may not be specific enough for IntelliSense to provide AutoComplete lists, such as with the ActiveSheet property or Sheets(#) property of a Workbook object in Excel. When you declare an object variable to use instead of the built-in sheet reference, you do get the appropriate IntelliSense lists.

Declaring an object variable can also help save you time by providing more information than you can get without them. Some built-in property references may not be specific enough for IntelliSense to provide AutoComplete lists, such as with the ActiveSheet property or Sheets(#) property of a Workbook object in Excel. When you declare an object variable to use instead of the built-in sheet reference, you do get the appropriate IntelliSense lists.

To make your macros as effective and effective as possible, and to reduce the likelihood of errors, always consider what you need the macro to accomplish and use the most specific method of reference available to accomplish the task.

Additional Resources

About the Author

Stephanie Krieger is a Microsoft Office System MVP and the author of two books, Advanced Microsoft Office Documents 2007 Edition Inside Out and Microsoft Office Document Designer. As a professional document consultant, Stephanie helps many global companies develop enterprise solutions for Microsoft Office on both platforms. She also frequently writes, presents, and creates content for Microsoft. You can reach Stephanie through her blog, arouet.net.