Office Space
From VBA Macro to Word Add-in
Robert Bogue
Code download available at: OfficeSpace2008_05.exe(166 KB)
Contents
Book Learning Macro Basics Moving Macros to VSTO Writing New Add-In Code More Buttons
Document automation isn’t a new functionality by any means. It has been going on since the invention of macros, and there has been a full-fledged programming model within Microsoft® Office applications since the early 1990s. For many years, the Office tools have featured macro recorders that weren’t limited to simply replaying keystrokes but have been capable of writing code on the fly as well. Macros have even demonstrated that they are functional enough to write viruses. And while writing a virus using a Word macro may be a dubious feat, it is a feat nonetheless.
However, even with all of the capabilities of Visual Basic® for Applications (VBA), there are some things it just doesn’t do very well. In the early days of VBA, XML hadn’t yet been invented, the Internet was in its infancy, and the first HTML pages were just beginning to appear. As such, it’s no wonder that making a Web service call isn’t well handled in the context of Visual Basic for Applications.
The Microsoft .NET Framework CLR, on the other hand, understands these technologies quite well. The ability to call Web services is just one among a number of reasons for wanting to write .NET-targeted code rather than VBA-and doing so means using Visual Studio® Tools for Office (VSTO). But switching from VBA to VSTO doesn’t have to be a case of throwing the baby out with the bath water; rather, it can be just a natural way to extend how you are already developing solutions in Office.
In this column you’ll see how to use Word to capture some VBA code that solves a basic problem. Then I will use the latest version of VSTO included with Visual Studio 2008 to wrap this code into a deployable Word add-in. I’ll also write some simple code for some of the tasks that the macro recorder either just can’t record or doesn’t record in an optimal way.
Book Learning
Recently, I found myself in a situation where this kind of VBA-to-VSTO conversion was perfect for the problem at hand. I was finishing my latest book, The SharePoint Shepherd’s Guide for End Users. The book is self-published, and I needed to output the manuscript from Word into a PDF that the printer could use. For that to work, there were several steps I first needed to complete to get the manuscript ready.
First, I had to assemble multiple files into one large document. With more than 140 individual files representing 13 sections and 116 tasks, munging them together wasn’t something I wanted to do by hand. Having so many individual files was great when working with Microsoft SharePoint® because it allowed for individual tracking of each task through the editorial workflow, but the sheer number made assembly a task best handled by automation.
Second, I wanted to make sure that all of the tracked changes in all of the documents had been accepted. During the editing process, I used revision marks (via the Track Changes feature in Word) to keep track of editing and other changes to the manuscript. The revision marks are supposed to all be accepted as part of the final checkout of the content, but if I missed some, the formatting of the revision marks would stand out in the final book, which wouldn’t be very professional looking.
I am going to illuminate the process of creating each of these pieces and the relative ability of the macro recorder to capture the code needed for each function. I’ll start by using the recorder to generate the basic automation code. From there I’ll take a closer look at workable but sub-optimal generated code. Finally, I’ll look at the recorder not generating code at all. I’ll convert the VBA code into VSTO code and put it into a Word add-in that I can use to assemble the final book. Just to make the process more challenging, I’ll convert the code from VBA to C# in the course of bringing it into VSTO.
Macro Basics
Figure 1** Enabling the Developer Tab in the Ribbon **
Figure 2** Record a Macro from the Developer Tab **
Figure 3** Naming the Macro **
Figure 4 AddFiles VBA Macro
Sub AddFiles() ' ' AddFiles Macro ' ' ChangeFileOpenDirectory _ "https://sharepoint.contoso.com/sites/sharepoint/" & _ "Shared%20Documents/SharePoint%20Tasks/" chúng tôi fileName:= _ "https://sharepoint.contoso.com/sites/SharePoint/" & _ "Shared%20Documents/SharePoint%20Tasks/" & _ "Task001%20-%20Create%20a%20Team%20Web%20Site.docx", _ ConfirmConversions:=False, _ ReadOnly:=False, _ AddToRecentFiles:=False, _ PasswordDocument:="", _ PasswordTemplate:="", _ Revert:=False, _ WritePasswordDocument:="", _ WritePasswordTemplate:="", _ Format:= wdOpenFormatAuto, _ XMLTransform:="" Selection.WholeStory chúng tôi chúng tôi Template:="Normal", NewTemplate:=False, DocumentType:=0 Selection.PasteAndFormat (wdPasteDefault) End Sub
Notice that there is an extraneous line that resets the next File Open directory. After that you see the Open command, and then there’s the series of commands to copy the text, create a new document, and paste the text into the new document. The code that the macro recording produced isn’t perfect, but it isn’t bad either. So I’ll take that, enhance it, and put it into VSTO.
Moving Macros to VSTO
To get started with VSTO, I first create a Word 2007 Add-in project. I open Visual Studio, start a new project, use the Word 2007 Add-in template for the project, and name the project PublishPrep. Upon successful creation of the new VSTO project, your Visual Studio 2008 environment should look similar to Figure 5.
Figure 5** New Word Add-In Project **
With the project created, I need to create a way for users to access the functionality in the add-in. For 2007 Office system applications, that means creating a Ribbon tab and buttons. First, add a new item to your project and select the Ribbon (Visual Designer) template from the Add New Item dialog. Name your new ribbon PublishPrep.
The next step is to customize the Ribbon. In this case, I’m just going to create a group that will live on the Add-ins tab instead of adding my own tab to the Ribbon. My group will contain three buttons.
Figure 6** Configuring the PublishPrep Ribbon **
Open the toolbox, scroll down to the Office Ribbon Controls group, and drag three button controls over onto the Publishing Preparation group. Note that the buttons will stack vertically by default.
Figure 7** Buttons and Group Configured for the Add-In **
Writing New Add-In Code
The first part of the code, a function called AppendFile (see Figure 8), takes a single parameter, the file name. At a quick glance the code doesn’t resemble the code the macro recorder wrote for me, but that’s mostly an illusion.
Figure 8 AppendFile
void AppendFile(string file) { if (string.IsNullOrEmpty(file)) return; Application app = Globals.ThisAddIn.Application; Document activeDoc = app.ActiveDocument; if (activeDoc == null) return; object fileObj = file; object confirmConversions = false; object readOnly = true; object addToRecentFiles = false; object passwordDocument = Missing.Value; object passwordTemplate = Missing.Value; object revert = true; object writePasswordDocument = Missing.Value; object writePasswordTemplate = Missing.Value; object format = Missing.Value; object encoding = Missing.Value; object visible = false; object openAndRepair = false; object documentDirection = Missing.Value; object noEncodingDialog = Missing.Value; object xMLTransform = Missing.Value; Document newDoc = app.Documents.Open(ref fileObj, ref confirmConversions, ref readOnly, ref addToRecentFiles, ref passwordDocument, ref passwordTemplate, ref revert, ref writePasswordDocument, ref writePasswordTemplate, ref format, ref encoding, ref visible, ref openAndRepair, ref documentDirection, ref noEncodingDialog, ref xMLTransform); app.Selection.WholeStory(); app.Selection.Copy(); activeDoc.Select(); object collapseEnd = WdCollapseDirection.wdCollapseEnd; app.Selection.Collapse(ref collapseEnd); app.Selection.Paste(); object saveChanges = WdSaveOptions.wdDoNotSaveChanges; object originalFormat = WdOpenFormat.wdOpenFormatAuto; object routeDocument = Missing.Value; newDoc.Close(ref saveChanges, ref originalFormat, ref routeDocument); object breakType = WdBreakType.wdPageBreak; app.Selection.InsertBreak(ref breakType); }
The first four lines just get the active document and perform error checking. If there’s no active document, you can’t exactly append to it, and if you don’t have a file name to append, there’s not much more you can do. The other thing that these lines are doing is getting references to the application-which is assumed in VBA-and the active document when the button method was called, something the recorded version of the macro didn’t need to do.
The next set of lines-the object variable declarations-are necessary because of the way C# makes the call to the COM components that Word exposes. I need to specify missing values, and since the values are all passed by reference, I need a variable to hold them.
The code then performs the same copy operation that the macro recorder generated. The real difference here is that my add-in code is responsible for the management of the active document, including making sure that the cursor position is set to the end of the active document.
The final block of code closes the document I opened, making sure not to save any changes. It also adds a page break after the inserted content because I want to make sure that the contents of the individual files don’t run together.
The second part of the code actually gets the list of files to assemble and calls AppendFile (see Figure 9). This isn’t code that the macro recorder captured, but it is where you see the power of VSTO because it allows you to leverage all of the .NET constructs. In this case, I’ll leverage the OpenFileDialog control, the ability to open and read from a text file, the use of generics to create a list of files, and calling another smaller method that iterates through the list.
Figure 9 VSTO Power Behind the Button
using Microsoft.Office.Interop.Word; using chúng tôi using System.Reflection;
Finally, replace the method stub created by Visual Studio with the code in Figure 9.
More Buttons
Figure 10** Enabling the Developer Tab in the Ribbon **
Sub AcceptAllChanges() ' ' AcceptAllChanges Macro ' WordBasic.AcceptAllChangesInDoc End Sub
The macro recorder recorded this action to use the WordBasic object. The WordBasic object is a holdover from before Word macros were modified to use VBA. It’s not the best way to approach the problem. It’s much better to use ActiveDocument. You can either employ the AcceptAllRevisions method or the Revision property’s AcceptAll method. I slightly prefer the Revisions.AcceptAll nomenclature, but either method works.
Here is the code necessary to accept revisions from within VSTO code:
One interesting thing to note is that the indexer here is 1-based, not zero-based as you would expect in C#.
Robert Bogue, Microsoft MVP for Microsoft Office Sharepoint Server, MCSE, and MCSA:Security, has contributed to more than 100 book projects and numerous other publishing projects. Robert blogs at chúng tôi you can reach him at Rob.Bogue@thorprojects.com. Find out more about his latest book project, The SharePoint Shepherd’s Guide for End Users, at sharepointshepherd.com.