Copying Table Row text using Word VBA

Automating a Microsoft Word document can make sense in certain situations, though due to misuse, Microsoft has found it necessary to limit the operation of ‘Macros’ within Word, Excel and other Office component products. With that in mind, here is something you might be able to use or adapt to your needs.

In the picture at the left, you can see that I have three tables in a Word 2000 sp3 document. Each table has a varying number of columns, and each cell within each table has text, with exception of the last column. Instead, the cell at the end of each row has a CommandButton control.

Note that you need to show the Control Toolbox in order to place the CommandButtons, and to switch in and out of Design Mode.

Word’s Macro Language is a form of Visual Basic known as Visual Basic for Applications (VBA). But VBA doesn’t support what’s know as Control Arrays in Visual Basic proper. So instead, each of the CommandButtons shown above need to have their own click-event procedure. Indeed, you can’t even override the default procedure name. So rather than repeating, or hard-coding specifics into each of these procedures, it’s better to try and tease-out the functionality and place it into a ‘Generic’ procedure instead – and that’s what I’ve done here.

There are eight CommandButtons in this Document. But the functionality of retrieving the text from each cell within that row lives in a procedure that I named GetTableText. Thus the only thing in the CommandButton‘s click-event procedure is a call to GetTableText, and nothing else. This makes it easy to add new tables, rows and columns – new buttons simply need to call the GetTableText procedure, and it takes care of the rest.

Here’s the code:

Private Sub GetTableText()
  If Not Selection.Information(wdWithInTable) Then Exit Sub

  'Get Column and Row of cell containing the button that launched this event.
  Dim c As Long, r As Long
  c = CLng(Selection.Information(wdStartOfRangeColumnNumber))
  r = CLng(Selection.Information(wdStartOfRangeRowNumber))

  'Get all text from each cell in this row, bar the current cell.
  Dim cels As Cells, i As Long, s As String
  cels = Selection.Range.Tables(1).Rows(r).Cells
  For i = 1 To c - 1
    s = s & StripJunk(cels(i).Range.Text) & vbTab
  Next
  s = Left(s, Len(s) - 1)

  MsgBox("|" & s & "|")
End Sub

Private Function StripJunk(ByVal s As String)
  StripJunk = Trim(Replace(s, vbCr & Chr(7), ""))
End Function

Private Sub CommandButton1_Click()
  GetTableText()
End Sub

Private Sub CommandButton2_Click()
  GetTableText()
End Sub

Private Sub CommandButton3_Click()
  GetTableText()
End Sub

Private Sub CommandButton4_Click()
  GetTableText()
End Sub

Private Sub CommandButton5_Click()
  GetTableText()
End Sub

Private Sub CommandButton6_Click()
  GetTableText()
End Sub

Private Sub CommandButton7_Click()
  GetTableText()
End Sub

Private Sub CommandButton8_Click()
  GetTableText()
End Sub

So how does this work ?

If Macros are enabled, and your not in Design mode, when you click one of the CommandButtons, it first selects the button, then calls any event procedures associated with the control. In this case, we have procedures for the click event, and each of these simply call the GetTableText procedure - in this case a Sub (Subroutine).

The GetTableText subroutine takes advantage of the fact that the Selection object changes with the act of clicking the CommandButton. The first thing, though, is that it checks that the current Selection is indeed within a Table - the very first If statement.

The next block of code recovers the row and column information of the current table cell, which is the cell where the CommandButton has been placed.

The third block of code recovers all the cells of the appropriate row of the the current table. It then iterates through each of the cells up until the column where the CommandButton was, recovering their text, and building up a string in the s variable. The text from each cell is separated with a tab character, and the StripJunk function is used to remove extraneous characters that are part of Word's housekeeping for table text.

Finally, the string is cleaned up (the last tab character stripped off), and the string is shown via a Message box. Additionally, I've also wrapped the Message box text with '|' characters, just to ensure that I am indeed cleaning up the string:

In the picture above, note that I've modified the last table by adding an extra column to the right of the CommandButtons. I did this to highlight the fact that the GetTableText subroutine only copies the text from the cells to the left of the cell which contains the CommandButton. In the picture, I clicked the CommandButton in the second row of the last table. Notice that the Message box only shows the text from columns 1 through to 4, and nothing from columns 5 and 6.

About these ads
This entry was posted in Uncategorized. Bookmark the permalink.