Image: PixelMe/Shutterstock
Microsoft Word tables are a powerful feature, and some documents have many. When working with lots of tables, it’s not uncommon for someone to come in at the end and say, “Can you change the tables so they’re all … ?”
SEE: 83 Excel tips every user should master (TechRepublic)
If you have a lot of tables and someone asks for a lot of formatting changes, you might panic: but don’t. Using VBA’s Tables collection, you can cycle through all the tables in a document and make the same change(s). In this article, I’ll show you two simple VBA procedures that cycle through the Tables collection. The first one converts each table to text. The second, changes the border color to blue.
I’m using Microsoft 365 on a Windows 10 64-bit system, but you can use earlier versions. VBA procedures aren’t supported by Word Online. For your convenience, you can download the demonstration .docm, .doc and .cls files.
Converting to text
A Word table is like any other table; it displays rows and columns of related data. Creating and formatting one is simple, but it’s just as easy to end up with inconsistencies from table to table when there are a lot of tables in a complex document. It takes a lot more time to reign in all those inconsistencies than it does to create them in the first place.
The procedure in Listing A is a simple For Each loop that cycles through all the tables in the current document. To do so, the code references the Tables collection and the Separator table property’s wdSeparateByTabs constant. It is extremely simple and does only this one thing: converts all tables to text. You are converting all tables—this is important to note because you might convert a table you don’t want converted. This procedure won’t let you pick and choose.
Listing A
Sub ConvertTblsToText()
‘Convert all tables to text.
Dim tbl As table
If ActiveDocument.tables.Count = 0 Then
MsgBox “There are no tables in this document.”, vbOKOnly, “Error”
Exit Sub
End If
For Each tbl In ActiveDocument.tables
‘wdSeparateByCommas, wdSeparateByDefaultListSeparator,
‘wdSeparateByParagraphs, wdSeparateByTabs
tbl.ConvertToText Separator:=wdSeparateByTabs
Next
End Sub
When converting a table to text, you have four delimiters to consider:
- wdSeparateByCommas
- wdSeparateByDefaultListSeparator
- wdSeparateByParagraphs
- wdSeparateByTabs
I’ve added these constants to the code as a comment, so it will be easier for you to adapt this code to your own work. Using the Tables collection, you could do so much with the tables; I chose to convert because it requires so little code. However, within that loop, you could change a single property for all tables or completely reformat all of them. The power is in the loop that gives you access to the Tables collection. From there, the possibilities are numerous.
SEE: Office 365: A guide for tech and business leaders (free PDF) (TechRepublic)
To enter the procedure, press Alt + F11 to open the Visual Basic Editor. In the Project Explorer to the left, select ThisDocument. You can enter the code manually or import the downloadable .cls file. In addition, the procedure is in the downloadable .docm, and .doc files. If you enter the code manually, don’t paste from this web page. Instead, copy the code into a text editor and then paste that code into the ThisDocument module. Doing so will remove any phantom web characters that might otherwise cause errors.
If you are using a ribbon version, be sure to save the workbook as a macro-enabled file. If you’re working in the menu version, you can skip this step. Now, let’s execute this procedure in the demonstration file shown in Figure A as follows:
- Click the Developer tab and then choose Macros in the Code group.
- In the resulting dialog, choose ConvertTblsToText and click Run.
Figure A
After running this procedure, all three tables are now plain text strings, separated by tabs, as shown in Figure B. Remember, if your delimiter isn’t tab characters, be sure to update that property in the code. In addition, if the document has no tables, the code will display an information message box and then stop. You’ll want to add a more descriptive message to your message box most likely.
Figure B
If you want to continue and you’re using the demonstration file, press Ctrl + Z three times to undo the table conversions. Or close the file without saving and reopen. We’re about to change table properties by expanding the procedure a bit.
Changing a format
Listing A cycles through the Tables collection, but you can do much more than convert the tables to text. You can apply a new table style, change a border color and so on. We’ll keep this next procedure, Listing B, as simple as the first by changing only one property, the outside border color.
Listing B
Sub ChangeTableBorderColor()
‘Change outside border color to blue.
Dim tbl As table
If ActiveDocument.tables.Count = 0 Then
MsgBox “There are no tables in this document.”, vbOKOnly, “Error”
Exit Sub
End If
For Each tbl In ActiveDocument.tables
tbl.Borders.OutsideColor = wdColorBlue
Next
End Sub
This procedure also cycles through the Tables collection, stopping at each table in the document and changing its outside border color to blue, as shown in Figure C. I chose this property because there are so many possibilities (and color constants). But once you know how to cycle through the Tables collection, it’s easy to make elaborate changes automatically by using VBA.
Figure C
There’s a bit of error-handling in both procedures, but you might want more. In addition, it’s unlikely that you’ll want to work through all those steps every time you want to run the procedure. Instead, add the macro to the Quick Access Toolbar. To do so, read How to add Office macros to the QAT toolbar for quick access.
