Web Development

Programming in VBA: Rip out the tables!

Looking for a practical example to use when you teach VBA? Bruce Maples has just the ticket.

I do lots of research on the Web. I mean lots! Typically, my modus operandi is to open a new Word document, fire up my browser, and head to one of my favorite search engines. When I find something I want, I copy it to the clipboard, and paste it into my Word document with a divider and a citation of the URL so I know where the information came from.

After doing this several times, I realized nearly every Web designer loves tables and frames. Tables and frames can be great for organizing Web content, but a pain when you copy them into Word. That’s because they often morph in unexpected ways, so you wind up with a table column that's one inch wide and 20 pages long.

At first, I ripped out these tables manually by selecting the table and choosing the Table menu’s Convert Table to Text option. After the first two, I thought, “This looks like a job for a good VBA tool.” Thus, RipOutTables was born.

How do I loop in VBA?
As any good programmer would, I started by thinking through exactly what I wanted my macro tool to do before I started writing code. The goal is pretty simple, actually: Go through my document and convert all the tables to text. So, the first thing I needed was a way to work through the document.

When faced with moving through a file and carrying out some action, all of us who cut our programming teeth in Xbase immediately think of using an end-of-file construct like


While NOT EOF()
 ' Do something fun here
Loop


Unfortunately, there isn't such a construct in VBA. (Actually, there is, but it’s a holdover from BASIC and used for manipulating a binary file—not something you’ll use every day.) So, how do we carry out a specific action across the entire file?

There are several ways, but the path I selected was to use the ActiveDocument object in the object model. (If you don’t know about object models, hang on—I’ll discuss them in a future article.) The ActiveDocument property is an easy way to refer to the current document (the document with focus). Additionally, it's a parent object to the Tables collection. So, if you use

ActiveDocument.Tables



you're referring to all the tables in the document with the focus.

To enumerate all the objects in a collection, you’d use the “For each” construct. Thus, to walk through all the tables in the Tables collection in the current document, you would use something like this:


For Each atable In ActiveDocument.Tables
 ' Do something even more fun here
Next atable


The use of “atable” isn't magic; it’s simply a variable used to reference each table as you get to it. You could just as easily have said “For Each foo.”

If you record the action of converting a table into a junk macro, the code you get is

Selection.Rows.ConvertToText Separator:=wdSeparateByParagraphs



Changing the “Selection” reference to our atable reference gives us the first version of RipOutTables, as shown in Listing A.


Listing A: RipOutTables—Version 1
Sub RipOutTables()
'
' RipOutTables Macro
' Macro to find and delete tables
'
Dim atable As Table

For Each atable In ActiveDocument.Tables

 atable.Select
 atable.Rows.ConvertToText Separator:=wdSeparateByParagraphs
Next atable

End Sub


A little Monica in my life, a little interaction in my code
The first time I ran my new macro tool, I was as pleased as any new parent. After all, the universal cry of the developer is, “It works!” Unfortunately, I was less thrilled as I scanned through my research and found places where tables had actually been used as tables rather than layout tools.

With the table removed and paragraph marks inserted between each cell’s data, all that valuable research data became a jumble of isolated words and numbers. I needed a way for my code to stop each time and ask before it zapped the current selected table.

Fortunately, it’s simple to put user interaction in your code. VBA comes with a number of tools for creating interaction, including the ability to build customized dialogs. I didn’t need anything fancy, though; simply a “Yes” or “No” to the question “Take out this one?” The Message Box function filled the bill nicely.

Here’s the syntax for the VBA Message Box function:

MsgBox(prompt[, buttons] [, title] [, helpfile, context])



The prompt argument is what you want the message box to say. The title argument is what title, if any, you want to show up in the title bar. Helpfile and context are used if you have a custom help file for your VBA application. Since any argument in brackets is optional, you can see that the only required argument is the prompt text.

Now a word about the buttons argument. This is a numeric expression (either an actual number or a numeric variable) that's the sum of the following: the number and type of buttons to display, the icon style to use, the identity of the default button, and the modality of the message box. Essentially, you look up the values of the buttons, icon, and modality you want, add them up, and put that number in the argument list.

If you’re thinking, “this is a pain,” you’re right. Fortunately, VBA comes with some predefined constants, and one set of those constants takes care of adding up all those numbers. Table A, taken from the Word VBA help file, shows some of those predefined constants. The first group of values (0–5) describes the number and type of buttons displayed in the dialog box; the second group (16, 32, 48, 64) describes the icon style; the third group (0, 256, 512) determines which button is the default; and the fourth group (0, 4096) determines the modality of the message box. When adding numbers to create a final value for the buttons argument, use only one number from each group.

Table A: Button arguments
Here’s a list of VBA constants for buttons and return values.


Simply find the “vb<whatever>” constants that cover what you want to do, and then add them together within the argument space. For example, if you wanted your buttons to be Yes/No and you wanted to use the Critical icon, you would write code something like this:


MsgBox(“Are you sure?”, vbYesNo + vbCritical, “Format C: Drive”)


Certainly easier than remembering the numbers, eh?

The MsgBox function returns a value indicating which button was pressed. Again, the value returned is numeric, and again, the VBA constants come to our rescue with a set of predefined constants for return values. Table B shows the range of return values.

Table B: MsgBox return values
Here’s a list of return values associated with the MsgBox function.


As you can see in Listing B—version 2 of RipOutTables— you check to see if the Yes button was pressed. If it was, you can convert the table to text and move on to the next table; otherwise, proceed to the next table (if one exists). Once you've worked through all the tables in the document, the For Each loop hits End Sub, and you're done.
Sub RipOutTables()
'
' RipOutTables Macro
' Macro to find and delete tables
'
For Each atable In ActiveDocument.Tables
 atable.Select
 If MsgBox("This one?", vbYesNo) = vbYes Then
 atable.Rows.ConvertToText Separator:=wdSeparateByParagraphs
 End If
Next atable

End Sub
Possible improvements
Since I wrote RipOutTables for my personal use, it's a quick and dirty piece of code. There's no error checking, and no easy way to exit before checking all the tables. If I were to recommend this to someone else, I'd add both these features. We’ll examine error trapping in another article, but for the early exit feature, I’ll refer you to the button constants and encourage you to figure it out for yourselves.

The amazing thing about VBA is you can get such wonderful returns on your investments. I estimate that this one tool has saved me at least 50 times the amount of time I put into writing it. Now that’s code with lots of bang for the buck!

Bruce Maples is an author, trainer, speaker, and consultant living in Louisville, KY. To comment on this article, please post a comment below or follow this link to write to Bruce.

0 comments