Taking the 2007 Office System Further with VBA and Open XML Formats
Summary: Learn the types of things that you can accomplish using the Microsoft Office extensibility tools including Microsoft Visual Basic for Applications (VBA) and Office Open XML. (6 printed pages)
Stephanie Krieger, arouet.net, Microsoft Office MVP
Applies to: Microsoft Office Excel 2007, Microsoft Office PowerPoint 2007, Microsoft Office Word 2007
Contents
Sure, You Can Do That with Office 2007!
What You Can Do with Visual Basic for Applications
What You Can Do with Office Open XML
Can You Go Even Further?
Additional Resources
About the Author
Sure, You Can Do That with Office 2007!
Have you ever needed to accomplish something in a Microsoft Office Word 2007, Microsoft Office Excel 2007, or Microsoft Office PowerPoint 2007 document that you didn't think you could do? Of course you have; we all have. So, here's the good news: there is an excellent chance that you were wrong, especially if you were working in the 2007 Microsoft Office system at the time. After all, just because something can't be done from within the user interface, does not mean that it can't be done.
There is plenty that you cannot do with the 2007 Office system. For example, Word is not very good at automotive engineering, Excel is not the best choice for creating cinematic special effects, and PowerPoint would not be my go-to program for monitoring astronauts in orbit. But, when it comes to your documents—well, that's a different story.
Since Microsoft Visual Basic for Applications (VBA) first became available in several Microsoft Office programs, it has been true that you can take documents further than you might expect. And, by pairing the Open XML Formats with the 2007 Office system, you can now take your documents further than ever before. Consider the following examples:
Do you need custom formatting that does not appear to be available, such as a different reflection height on a PowerPoint graphic or different page number formatting in a Word document table of contents?
Do you wish there was a way to save time on repetitive tasks (such as formatting dozens of tables in a Word document at once) or recover content that you thought you had lost (such as the source data for an Excel chart)?
Do you need something more complex, such as custom automation with a custom interface that can help your organization create content more easily and with more consistent results?
Or, do you just want a way to have more fun with the 2007 Office system programs (perhaps by building your own computer game in Excel)?
From simple tasks to complex solutions, there is a very good chance that you can do more with the 2007 Office system, in less time, and with less work than you expect.
What You Can Do with Visual Basic for Applications
From recording macros to building custom add-ins, and everything in between, the thing that may surprise you the most about VBA is how much you already know just by being an advanced user of the Microsoft Office programs. Check out the subsections that follow to learn about progressing from recording to writing and editing your own macros, and to discover how much you already know.
.gif "Note") Note |
|---|
The examples in this article focus on Word, Excel, and PowerPoint, but VBA is available in many of the client programs in the 2007 Office system. If you work with Microsoft Office Access 2007, you probably already know that VBA can be an extremely important and powerful component of that application when building custom database solutions. Additionally, VBA is available in Microsoft Office Outlook 2007, Microsoft Office Project 2007, Microsoft Office Visio 2007, Microsoft Office Publisher 2007, and Microsoft Office SharePoint Designer 2007. |
Recording Macros
If you have recorded macros in any Microsoft Office program, you know that recording macros is a handy way to reduce repetitive actions. For example, perhaps you need to format 30 tables in a Word document and each of those tables requires the following:
Light Shading - Accent 4 table style.
Heading 3 paragraph style applied to the heading row.
All columns set to 0.6 inches wide.
AutoFit set to Fixed Column Width.
Table horizontally centered on page.
You can record the macro while performing those actions on the first table, and then click into the heading row of each subsequent table and run the macro instead of manually repeating this set of actions for each table. When you start the macro recorder, you can name the macro, specify the document or template where you want to store the macro, and attach the macro to either a keyboard shortcut or (in Word or Excel) a Quick Access Toolbar button for easy access.
Without writing any code, you just used VBA to save a substantial amount of time.
| Note |
|---|
In the 2007 Office system, macro recorders are available in Word, Excel, Visio, and Project. |
Writing or Editing Macros
The downside to recording macros, of course, is that a recorded macro can only act on a selection. That is, using the preceding example, you have to click into the heading row of each of the remaining 29 tables and run the macro separately each time. Much faster than performing the tasks manually for all 30 tables, but still time-consuming.
Instead, if you learn just a bit of basic VBA, you could edit that recorded macro in less than a minute to add just a few lines of code that would enable you to run the macro once to instantly format all 30 tables.
Sound too good to be true? Take a look for yourself. Following is the code that the macro recorder wrote for you, which you will have to run 29 times to format all of your tables:
Selection.Tables(1).Style = "Light Shading - Accent 4"
Selection.SelectRow
Selection.Style = ActiveDocument.Styles("Heading 2")
Selection.Tables(1).Select
Selection.Tables(1).AutoFitBehavior (wdAutoFitFixed)
Selection.Tables(1).AutoFitBehavior (wdAutoFitFixed)
Selection.Tables(1).Rows.Alignment = wdAlignRowCenter
Selection.Columns.PreferredWidthType = wdPreferredWidthPoints
Selection.Columns.PreferredWidth = InchesToPoints(0.6)
| Note |
|---|
The duplicate AutoFitBehavior statement shown here is generated by the macro recorder but it is unnecessary, as you see in the edited version of this macro that follows. |
Following is a basic version of the code for the edited macro that you run once with your insertion point anywhere in the document:
Dim oTb As Table
For Each oTb In ActiveDocument.Tables
oTb.Style = "Light Shading - Accent 4"
oTb.Rows(1).Range.Style = ActiveDocument.Styles("Heading 2")
oTb.AutoFitBehavior (wdAutoFitFixed)
oTb.Rows.Alignment = wdAlignRowCenter
oTb.Columns.PreferredWidth = InchesToPoints(0.6)
Next oTb
When you compare these two code samples, do you notice that the edited macro with the extra code is actually shorter than the recorded version? That is because when you record a macro, you sometimes record actions that are not relevant to your specific task just because of how you access those features (such as through a dialog box that records several settings when you click OK). In fact, a more experienced VBA editor might make this code even simpler so that they don't have to repeat the table variable (oTb) on each line. (One of the first rules that I learned about writing VBA was that you almost never have to write the same thing twice.)
But, the most important thing to notice when you look at either of these code samples is how much you already know even if you have never before seen a line of code. The best thing about VBA is that it is an extension of the built-in functionality in the applicable program. So, when you know features such as tables, rows, styles, alignment, and AutoFit behavior—you already know quite a lot of what you need to know to get started.
| Note |
|---|
To learn how to edit the preceding macro for yourself, and other timesaving tips for using VBA to format documents, see Using VBA to Format Long Documents in the 2007 Office System in a Fraction of the Time. |
How Far Can You Take VBA?
After you learn the basics of writing and editing VBA macros, you can use VBA to save time on everyday tasks (like the example shown previously) and to do things that can't be done from within the user interface (such as a custom table of contents or retrieving lost chart data, as also mentioned previously). As your VBA skill develops, you can even build your own custom templates and add-ins with your own dialog boxes, custom behavior for built-in features, and interaction between multiple programs in the 2007 Office system.
If VBA is brand new to you, check out Additional Resources for resources to help you get started. Then, take a look at the articles that follow for some how-to examples:
Extending Word 2007, Excel 2007, and PowerPoint 2007 with a Single Line of VBA Code
Using VBA to Control Built-In Commands in the 2007 Office System
Using VBA to Access Document Content in the 2007 Office System
Also check out Microsoft Knowledge Base Article ID: 300643—Macro to extract data from a chart in Excel, which contains a macro and associated steps to programmatically extract data from an Excel chart. An earlier version of this article was one of the first examples I saw years ago that sold me on the value of learning VBA.
What You Can Do with Office Open XML
If you use the 2007 Office system, you probably already know about the file formats, called Open XML Formats, introduced in that version for Word, Excel, and PowerPoint. But, you may not know that you can break into the XML that's under the hood of your document to do everything from custom formatting to document troubleshooting to creating custom content.
A document that uses the Open XML Formats is actually a compressed collection of files and folders that enables you to access virtually all of the content, formatting, and settings stored in your file. A user of the 2007 Office system never has to see this file structure or even know that it exists to use Word, Excel, and PowerPoint effectively. But consider some examples of what you can do when you choose to explore the Office Open XML structure behind your documents:
Customize formatting, such as changing the height of a shape reflection or changing the tints of theme colors used in your styles.
Update repeated content with a single copy and paste—such as replacing a logo that appears 20 times in the same document by just pasting the new image into the ZIP package once.
Customize the Office Fluent Ribbon.
Create custom theme effects.
Customize or even build your own SmartArt layouts.
Add and update external data in your documents.
In addition to how much you can do, however, the most important things you may want to know about Office Open XML are about how much you already know:
One of the most popular benefits of Office Open XML is that it's written in very straightforward language that is easy to read and understand.
Although you gain additional capabilities when you use a specialized XML editing environment (such as Microsoft Visual Studio 2008 or even Microsoft XML Notepad 2007), all you actually need to perform any of the Open XML tasks mentioned earlier is a simple text editing program like Notepad.
Check out Additional Resources for links to basic resources that can help you get started breaking into the Open XML Formats behind your documents and learning how to read and edit Office Open XML markup. Then, explore the articles that follow for some specific how-to examples:
Using VBA to Enumerate All Word 2007 Documents Containing Revisions and Comments
Using Office Open XML to Customize Document Formatting in the 2007 Office System
Getting More from Document Themes in the 2007 Office System with Office Open XML
Can You Go Even Further?
If you are new to Microsoft Office extensibility, or even if you have been using VBA and Office Open XML for some time, it's likely that VBA and Office Open XML offer enough power and flexibility to keep you happy for a very long time without ever venturing further than the Microsoft Visual Basic Editor or Notepad.
But, if you already have experience with VBA or Office Open XML and you're wondering how you can take Microsoft Office development even further, the answer is to explore creating professional development solutions using managed code. To get started (and continue) learning about professional-level Microsoft Office development, explore the MSDN Office Developer Center.
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 andMicrosoft Office Document Designer: Your Easy-to-Use Toolkit and Complete How-To Source for Professional-Quality Documents (Bpg-Other). 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.