Monday, May 19, 2014

Word Automation - Create Table, Merge Cells

I was pondering a question inside foxite forum about word automation because of the handicap presented by MS that when recording a macro in Word, objects cannot be clicked using mouse.

So the first solution that I can think of is to use keyboard as that is still allowed and that to customize Quick Access Toolbar to include an icon for merging so I can click (and merge cells) for later reviewing of macro recordings.  And this resulted to these codes:



Sub Macro1()
'
' Macro1 Macro
'
'
    ActiveDocument.Tables.Add Range:=Selection.Range, NumRows:=3, NumColumns:= _
        5, DefaultTableBehavior:=wdWord9TableBehavior, AutoFitBehavior:= _
        wdAutoFitFixed
    With Selection.Tables(1)
        If .Style <> "Table Grid" Then
            .Style = "Table Grid"
        End If
        .ApplyStyleHeadingRows = True
        .ApplyStyleLastRow = False
        .ApplyStyleFirstColumn = True
        .ApplyStyleLastColumn = False
        .ApplyStyleRowBands = True
        .ApplyStyleColumnBands = False
    End With
    Selection.MoveDown Unit:=wdLine, Count:=1, Extend:=wdExtend
    Selection.MoveRight Unit:=wdCharacter, Count:=2, Extend:=wdExtend
    Selection.Cells.Merge

End Sub

From there we can get the values of the constant by right-clicking on a constant word and choosing Quick Info.  Then later cleaning up the codes.  But I don't like the way it shows that we have to move this way and that so believing there is a way to reference the cells of the table directly as we do in Excel, I continued by surfing hoping to find any codes (VFP, VB, C#, etc.) that we can study, clean and convert into VFP.

And there is not much to dig into because only a few maybe would go for word automation being presented with tons of options to do things.  I found a good one for VFP though here: https://support.microsoft.com/kb/250501/EN-US

Anyway, further surfing the net looking at some PL's codes, I realize we can do it like this:

* Merge whole row 8
oTable.Rows(8).Cells.Merge

But this means we have no control over the cells.  It merges the entire row but we don't need that most of the times.  Finally I bumped into another and converting and cleaning the codes into our VFP, goes something like this:

*Merge by means or range
With oWord.Selection
      oRangeStart = .Tables(1).Cell(1,1).Range.Start
      oRangeEnd = .Tables(1).Cell(4,3).Range.End
      .SetRange(m.oRangeStart,m.oRangeEnd)
      .Cells.Merge
Endwith

That is what we need, a better control on which cells to merge.

But then, why go to the above approach when we can simply do it this way?

* Simpler way
oTable.Cell(10,1).Merge(oTable.Cell(10,3))


Whole Test Codes:

Local oWord As Word.Application , oDoc, oRange, oTable, oRangeStart, oRangeEnd
oWord = Createobject("Word.Application")

* Create a docomunet
oDoc = oWord.Documents.Add()

* Create a Range
oRange = oDoc.Range()

* Create table based on range (10 rows & 4 columns)
oWord.ActiveDocument.Tables.Add(oRange, 10, 4, 1, 0)
oTable = oWord.ActiveDocument.Tables(1)

* Merge whole row 8
oTable.Rows(8).Cells.Merge


*Merge by means or range
With oWord.Selection
      oRangeStart = .Tables(1).Cell(1,1).Range.Start
      oRangeEnd = .Tables(1).Cell(4,3).Range.End
      .SetRange(m.oRangeStart,m.oRangeEnd)
      .Cells.Merge
Endwith

* Simpler way
oTable.Cell(10,1).Merge(oTable.Cell(10,3))

oWord.Visible = .T.


Gotcha!

Merging of table cells like what is shown above may sometimes fail depending on the current structure of your word table.  In some ways, it will fail because there is already previously merged on top of the cells we want to merge that merging new cells will conflict with it.  In which case, the solution is to split those cells back up and then recreate merging.

Russel Hill explained it up nicely with these words:

I have encountered this issue in some Word Automation of late. The problem is that Word handles the "numbering" of table cells differently than Excel when it comes to merges.

In the Excel case if you merge the cells (2,2) to (2,4) would still refer to the 2nd row/5th column using (2,5), whereas in the Word table case you would need to use (2,3).

Here is an example of what I needed to do in Word to merge columns 2 to 4 and 5 to 7 (in row 2 of a table):


oTable2.Cell(2,2).Merge(oTable2.Cell(2,4))
oTable2.Cell(2,3).Merge(oTable2.Cell(2,5))

Notice that the first merge makes perfect sense but that columns 5, 6, 7, ... are thereafter considered to be columns 3, 4, 5, ... as Word seems to treat the number of columns in a row as the number of distinct compartments taking merges into account 

So in a row of m columns if you merge n cells the number of columns becomes ...  m-n+1





1 comment:

  1. Hi,

    I do not have any macro skills but I know the power of macros to speed up the work. I need your help to create a macro which will help me to merge to cell without mergeing the two rows. My documents has more than 100 rows and I want to merge first two columns in each row. Please suggest if I can tacke this using a word macro. If yes, please share the macro.

    ReplyDelete